INSERT

Question

Post reply

INSERT

billy24

Old Hand

Points: 301
More actions
April 18, 2011 at 7:45 am

#234921

Hello,
I am trying to insert some rows from dbo.productstb to dbo.stocktb, where in dbo.productstb productid is identity column and in dbo.stocktb stockid is the identity column, following below is my query
set IDENTITY_INSERT [dbo].[stocktb] ON
INSERT INTO [dbo].[stocktb] (
[productid])
SELECT
[ProductID]
FROM dbo.productstb where ProductID between 62603 and 63088
set IDENTITY_INSERT [dbo].[stocktb] OFF
and when i run above query it gives me error saying:
Msg 545, Level 16, State 1, Line 6
Explicit value must be specified for identity column in table 'Stocktb' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
any solution to this problem??
Thanks in advance.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

April 18, 2011 at 7:54 am

#1312786

Do you have replication turned on?

Lowell SSC Guru Points: 323546 More actions · Answer 2

what is the name of the identity column in the table [stocktb]?

are you trying to force stocktb.[identitycolumn?] to be exactly the same as the productid from the other table, or just enter the new records?

assuming the [stocktb] might have the columns [stockid],[productid],

simply removing everything related to identity insert should work:

INSERT INTO [dbo].[stocktb] (

[productid])

SELECT

[ProductID]

FROM dbo.productstb where ProductID between 62603 and 63088

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

billy24 Old Hand Points: 301 More actions · Answer 3

Lowell (4/18/2011)
what is the name of the identity column in the table [stocktb]?
are you trying to force stocktb.[identitycolumn?] to be exactly the same as the productid from the other table, or just enter the new records?
assuming the [stocktb] might have the columns [stockid],[productid],
simply removing everything related to identity insert should work:
INSERT INTO [dbo].[stocktb] (
[productid])
SELECT
[ProductID]
FROM dbo.productstb where ProductID between 62603 and 63088

identity column in dbo.stocktb is stockid and I am not trying to force it same that of productid, well stockid is the unique id and it should be incremented by itself

billy24 Old Hand Points: 301 More actions · Answer 4

Ninja's_RGR'us (4/18/2011)
Do you have replication turned on?

no I am not doing any replication here, I am trying to just insert the data from one table to another and the identity column should get incremented by itself

billy24 Old Hand Points: 301 More actions · Answer 5

Lowell (4/18/2011)
what is the name of the identity column in the table [stocktb]?
are you trying to force stocktb.[identitycolumn?] to be exactly the same as the productid from the other table, or just enter the new records?
assuming the [stocktb] might have the columns [stockid],[productid],
simply removing everything related to identity insert should work:
INSERT INTO [dbo].[stocktb] (
[productid])
SELECT
[ProductID]
FROM dbo.productstb where ProductID between 62603 and 63088

hey thank you very much this worked fine..