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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy