Solved

insert into SQL 2005 table

Posted on 2015-01-22
3
192 Views
Last Modified: 2015-01-22
Hello! I am executing the following insert query:

insert into table1 select * from table2

If I get no errors and it completes successfully, can I assume all the imported records came in unchanged from table1? I tried to import table2 into table1 but I go the Identity error since I have a rownumber field in table1. I don't care which table it is though as long as both tables are combined. Can I expect that my records combined without issue in table2?
0
Comment
Question by:mrosier
3 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40565340
>can I assume all the imported records came in unchanged from table1?
yes

>I tried to import table2 into table1 but I go the Identity error since I have a rownumber field in table1
Then you're going to have to list all columns, except for the identity, that you're ok with new identity values.
Unfortunately there is no SELECT * (except the id column) syntax.

Using the * in an insert is not a best practice, as it is dependant on the schema of the two tables to be EXACTLY the same, and forces an insert on all columns.

>I don't care which table it is though as long as both tables are combined.
Ok.  Based on why you're asking another option is to create a view that UNIONs the two tables.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40565455
If you're importing all columns from table2, you can use * there, you just have to list the columns for table1:

insert into table1 ( col1, col2, ... )
select * from table2


If table2 contains a numeric value you want to use for the rownumber, you can specify:
SET IDENTITY_INSERT 'dbo.table1' ON
and load the existing values from table1 instead of generating new identity/rownumber values.
0
 

Author Closing Comment

by:mrosier
ID: 40565658
this worked perfectly, thank you!!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question