SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using SELECT to INSERT records
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/04/2000 :  05:36:30  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Edwin writes "Ok, this may be simple to some, but it sure is a puzzle to me. I want to move a set of data from one table to another table with a similar structure. What I figure was a shady solution: Selecting the data from the source table, then opening the other table and using a loop to populate the destination table. Question: Is there a way I can use just ONE insert statement to do all this? Sort of like incorporating the select statement into the insert statement? If there is one, then please give me some example code." We sure can do this in one statement (but looks like two).

Read Using SELECT to INSERT records

Anonymous
Starting Member

0 Posts

Posted - 08/18/2000 :  11:20:36  Show Profile  Reply with Quote
Edwin : Using select to insert records..

Hi Edwin,

If your tables are EXACTLY the same and they do not have any constraints that would prevent a direct insert, then you may use:

INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)
WHERE Field10 = (some condition)

This is a clean and easy way to copy data.

Thanks

victor mensah

Go to Top of Page

bjcramer
Starting Member

USA
0 Posts

Posted - 04/26/2001 :  11:11:36  Show Profile  Send bjcramer an AOL message  Send bjcramer an ICQ Message  Send bjcramer a Yahoo! Message  Reply with Quote
If your fields don't exactly match...

I recently ran into a similar situation as the original post. However, my destination table had a required field that wasn't present in the source table schema.

As an example, the destination table has a required field that identifies the business unit who added the record, and the source table tracks business groups (business units are subsets of the business groups) by another identifier.

I had to populate the bu_id column with a business unit id. So, I modified my source table select statement to accomodate the insert.

Here's the code:
insert into BU_SECTION_LINE
(bu_id, section_id, name, short_desc, date, meta_keyword,
auth_req, graphic_request, graphic_completed, city, state_province,
country, region, attr_flag_1, attr_flag_2, attr_flag_3, old_id,
old_table_name, attr_flag_4)
select 24 bu_id, section_id, name, short_desc, date, meta_keyword,
auth_req, graphic_request, graphic_completed, city,
state_province, country, region, attr_flag_1, attr_flag_2,
attr_flag_3, old_id, old_table_name, NULL attr_flag_4
from GROUP_SECTION
where group_section_id > 1

GROUP_SECTION does not contain the required column bu_id, but it is required by BU_SECTION_LINE. So I gave it the desired value and aliased the value with the BU_SECTION_LINE name. You may also recognize that the attr_flag_4 has NULL in front of it in the select statement. It isn't a required field, but the insert must include it.

Any field can be filled in this way.

Go to Top of Page

jraha
Starting Member

USA
16 Posts

Posted - 05/23/2001 :  13:29:09  Show Profile  Reply with Quote
When I try to use this type of syntax
insert #tempTable (usernum)
select usernum from tUsers

I get the following error message:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temptable'.

This only happens on one of my databases though. Creating the #tempTable implicitly works on other databases that I'm using. What setting am I missing?

Thanks,
Joel

Go to Top of Page

k.i.p.
Starting Member

USA
2 Posts

Posted - 05/23/2001 :  15:13:45  Show Profile  Reply with Quote
Joel,
Try insert into
That might work.
Regards,
Luke

Edited by - k.i.p. on 05/23/2001 15:30:58
Go to Top of Page

Funkenstein
Starting Member

Norway
1 Posts

Posted - 05/27/2001 :  19:59:15  Show Profile  Visit Funkenstein's Homepage  Reply with Quote
How can this be done when dealing with 2 different databases ? For example to copy a table from a sql-server to access ?
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/28/2001 :  09:38:50  Show Profile  Visit nr's Homepage  Reply with Quote
into in insert into is optional i.e. doesn't affect the query.

insert #tempTable (usernum)
select usernum from tUsers
gives Invalid object name '#temptable'

This is because the temp table doesn't exist
either select usernum into #tempTable from tUsers or create the temp table first.

How can this be done when dealing with 2 different databases.
insert dbname1.owner1.tbl1 (cols...) select (cols...) from dbname2.owner2.tbl2

For example to copy a table from a sql-server to access
Could probably do this by creating a linked server to the access database and fully qualifying the table name
linkedserver.database.owner.table.


Go to Top of Page

rahul
Starting Member

1 Posts

Posted - 04/14/2002 :  01:44:35  Show Profile  Reply with Quote
Hello,

Can anyone tell me how to insert file into sql server database with servlet or jsp as scripting language?

Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 04/14/2002 :  07:56:46  Show Profile  Visit robvolk's Homepage  Reply with Quote
What kind of file are you inserting? Is it a comma-separated/tab-demilited, or other kind of text file? You can use DTS or BULK INSERT to import the file into your SQL Server tables. Books Online has more detail. You would have to upload the file to the SQL Server if you are going to use BULK INSERT.

Go to Top of Page

MikeR
Starting Member

2 Posts

Posted - 04/25/2002 :  05:00:58  Show Profile  Reply with Quote
quote:

How can this be done when dealing with 2 different databases ? For example to copy a table from a sql-server to access ?

Use SQL Server's Data Transformation Services

Mike

Go to Top of Page

stan
Starting Member

1 Posts

Posted - 02/18/2003 :  13:07:35  Show Profile  Reply with Quote
Okay, this is just a syntax question, but I can't find the answer anywhere...

I know the syntax for inserting values into a table and I know the syntax for inserting the results of a select statement into a table.
But, I need to combine these two methods and I've done it before but can't remember how to do it!

I have so far:
INSERT INTO Members ( memberID, memberareaID )
VALUES(@memberID,(SELECT memberareaID FROM MemberAreas WHERE areadescription = '@areadescription'))

The only difference is that, previously, I had this working in SQL but now I'm doing it in MS Access so maybe that's why this is not working???

Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 02/19/2003 :  08:42:02  Show Profile  Visit mohdowais's Homepage  Reply with Quote
quote:

I know the syntax for inserting values into a table and I know the syntax for inserting the results of a select statement into a table.
But, I need to combine these two methods and I've done it before but can't remember how to do it!

I have so far:
INSERT INTO Members ( memberID, memberareaID )
VALUES(@memberID,(SELECT memberareaID FROM MemberAreas WHERE areadescription = '@areadescription'))




Try this:

INSERT INTO Members ( memberID, memberareaID )
SELECT @memberID, memberareaID FROM MemberAreas WHERE areadescription = @areadescription

OS

Edit: You might want to move this discussion from here, start a new thread.




Edited by - mohdowais on 02/19/2003 08:43:56
Go to Top of Page

Bambola
Posting Yak Master

Italy
103 Posts

Posted - 02/19/2003 :  08:50:04  Show Profile  Reply with Quote
SELECT *
INTO new_table
FROM my_table

will create a duplication of my_table and insert all rows.
I would, however, create the table explicitly.

Bambola.

Go to Top of Page

domagoj
Starting Member

5 Posts

Posted - 02/19/2003 :  08:51:00  Show Profile  Reply with Quote
OK. but is there statement that will actually MOVE (copy+delete) a record?

Go to Top of Page

Bambola
Posting Yak Master

Italy
103 Posts

Posted - 02/19/2003 :  09:02:53  Show Profile  Reply with Quote
quote:

OK. but is there statement that will actually MOVE (copy+delete) a record?



I don't think so. You will have to do it yourself (within a transaction)

Bambola.

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/19/2003 :  17:00:10  Show Profile  Reply with Quote
A Logical MOVE and COPY would be treated a single unit of work with in the same transaction...something like:

BEGIN TRAN

INSERT INTO Table1 (key, col1, col2, ect) SELECT key, col1, col2, ect From Table2 Where key = something

Delete From Table2 Where key = something

COMMIT TRAN

Go to Top of Page

jourdan
Starting Member

1 Posts

Posted - 05/13/2003 :  17:19:42  Show Profile  Visit jourdan's Homepage  Reply with Quote
how do you copy sone rows but in the same table??

regards

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/13/2003 :  17:26:38  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Exactly the same as if it were coming from another table... with the INSERT...SELECT syntax:

INSERT INTO yourTable (f1, f2, f3)
SELECT f1, f2, f3
FROM yourTable
WHERE ... some condition ....



- Jeff
Go to Top of Page

rriordan
Starting Member

3 Posts

Posted - 07/19/2003 :  01:39:57  Show Profile  Reply with Quote
Hey. Thanks for looking at this.

I tried the generic insert from near the top of this thread to copy from one table to another. I have two tables that are EXACTLY the same, save for their name. When I tried the SQL:

INSERT INTO main_a
SELECT * from main_b

I get the following error (from Query Analyser RUN):
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

I am trying to avoid explicitly typing all the field names into the statement (there are about 50 of them). Can anyone help?

Go to Top of Page

rriordan
Starting Member

3 Posts

Posted - 07/19/2003 :  01:43:24  Show Profile  Reply with Quote
Please disregard the above plea for help. Sorry. I redesigned main_a but forgot to do the same operations on main_b. My bad.

Go to Top of Page

jimdalrymp
Starting Member

1 Posts

Posted - 07/21/2005 :  12:57:57  Show Profile  Visit jimdalrymp's Homepage  Reply with Quote
What if you are inserting a large number of records from one table to another using INSERT SELECT and there is a failure on one or more of the records? Will the whole process be rolled back? If not, how do I know which records failed? This is the reason I use a cursor, but if there are ways to identify failing records, I would love to use a select statement instead. Thanks for the help.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000