• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

insert into SQL 2005 table

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
mrosier
Asked:
mrosier
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Scott PletcherSenior DBACommented:
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
 
mrosierAuthor Commented:
this worked perfectly, thank you!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now