Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
robg69
Starting Member
41 Posts |
 Posted - 2004-03-22 : 11:37:01
|
| Hi, I was trying to insert data from a (linked)production table to a development table. The tables are identical, so I just did an: insert into DEV_TABLE select columns from PROD_TABLE This worked fine, however I tried to do it with the following to force the original identity columns: "SET IDENTITY_INSERT DEV_TABLE " ON/OFFand it didn't work. If I go into EM and turn it off, do the insert then turn it back on it works fine. So my question is, why didn't the command work from QueryAnalyzer? I also tried it with select *. Can you not use insert/select with the set identity_insert?I was just curious, Thanks! |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-22 : 12:02:22
|
| Can you send us the actual script and the error.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
robg69
Starting Member
41 Posts |
Posted - 2004-03-22 : 12:18:28
|
| OK, I'm an idiot, I figured it out. I wasn't including the column list in the insert AND the select, just the select. It should have been:SET IDENTITY_INSERT Users ONinsert into Users (UserID, UserName)select UserID, UserName from [PRODLS].test.dbo.UsersSET IDENTITY_INSERT Users OFF |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-22 : 12:37:20
|
| You got lucky?Post the DDL...I would also supply a column list with the DML you postedBrett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-03-22 : 12:38:38
|
| See what happens when you go to lunch...Brett8-) |
|
|
|
|
|
|
Turning on/off Identity column...