March 21, 2012 at 12:17 pm
I have two tables A and B with the same structure
and I am trying to copy selected records from table A to table B including the identity column values
(here identitycolumn is 'reqid')
SET IDENTITY_INSERT dbo. ON
insert into dbo.
SET IDENTITY_INSERT dbo. OFF
select * from dbo.[A] where reqid = 201
I keep getting the error 'Incorrect syntax near the keyword 'SET'
Can someone please let me know what I am doing wrong? thanks!
March 21, 2012 at 12:26 pm
This:
SET IDENTITY_INSERT dbo. ON
insert into dbo.
select * from dbo.[A] where reqid = 201
SET IDENTITY_INSERT dbo. OFF
March 21, 2012 at 2:36 pm
I get the following error when I modified it the way you suggested
'An explicit value for the identity column in table 'DGSData.dbo.DocumentHoldBackup' can only be specified when a column list is used and IDENTITY_INSERT is ON.'
March 22, 2012 at 4:12 am
Annee (3/21/2012)
I get the following error when I modified it the way you suggested'An explicit value for the identity column in table 'DGSData.dbo.DocumentHoldBackup' can only be specified when a column list is used and IDENTITY_INSERT is ON.'
As the error itself states, you will have to list the names of all the columns if you are using the IDENTITY_INSERT option
SET IDENTITY_INSERT dbo. ON
insert into dbo.( col1, col2, col3, ..., coln ) --List the names of columns
select * from dbo.[A] where reqid = 201
SET IDENTITY_INSERT dbo. OFF
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 27, 2012 at 8:39 pm
MODIFY THE QUERY AS BELOW TO INSERT INTO IDENTITY COLUMN
SET IDENTITY_INSERT dbo. ON
GO
insert into dbo.
GO
SET IDENTITY_INSERT dbo. OFF
GO
select * from dbo.[A] where reqid = 201
TO FIX YOUR OTHER ERROR ('An explicit value for the identity column in table 'DGSData.dbo.DocumentHoldBackup' can only be specified when a column list is used and IDENTITY_INSERT is ON.'), You have to specify all the column names in your insert as shown below:
INSERT INTO DGSData.dbo.DocumentHoldBackup
(IDENTITY_COLUMN_NAME, COLUMN1, COLUMN2)
VALUES(
VALUE, VALUE_1, VALUE_1)
If you would like to look at more information on IDENTITY COLUMNS, you could go to [/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply