August 10, 2010 at 7:39 am
Hello, I'm trying to refresh our development enviornment from production and I have tables with identity fields. When you use the wizard to import the data, it has a flag for setting the identity_insert and when I use the wizard and run the refresh it works... however, I'm trying to create a stored procedure that does the refresh with a linked server instead so I have more control over the various tables and such.
However, when I set the identity_insert flag before inserting to the table...it doesn't work. No matter what I do I still get the error message:
Msg 8101, Level 16, State 1, Line 5
An explicit value for the identity column in table '1099_EXTRACT' can only be specified when a column list is used and IDENTITY_INSERT is ON.
This is my code:
TRUNCATE TABLE [tablename]
SET IDENTITY_INSERT [tablename] ON
INSERT INTO [tablename]
SELECT [ID],
[VENDOR_ID]
,[TAX_ID]
,[PYMT_NO]
,[NAME]
,[ADDR]
,[ADDR2]
,[ADDR3]
,[ADDR4]
,[CITY]
,[ST]
,[ZIPCODE]
,[PHONE]
,[VENDOR_TYPE]
,[NO]
,[PGM]
,[PYMT_AMT]
,[PYMT_DATE]
,[TAX_YR] FROM [linkedservername].[database].[schema].[tablename]
The set command appears to be working but for some reason it seems like it is ignoring it when I run the above code.
Any ideas?
August 10, 2010 at 7:48 am
well the error is telling you you have to explicitly list the column names:
TRUNCATE TABLE [tablename]
SET IDENTITY_INSERT [tablename] ON
INSERT INTO [tablename](COLUMN1,COLUMN2,COLUMN3....)SELECT [ID],
[VENDOR_ID]
,[TAX_ID]
,[PYMT_NO]
,[NAME]
,[ADDR]
....
Lowell
August 10, 2010 at 8:27 am
Oh Duh... I thought it was saying I had to list them in the select statement... haha... brain fart for the day is over... hopefully. 😉
December 17, 2014 at 5:35 am
Thank you! This one caught me out today!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply