IDENTITY INSERT PROBLEM - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

IDENTITY INSERT PROBLEM

Author

Topic

nshovater
Starting Member

6 Posts

Posted - 2009-01-08 : 11:46:15

I have a table created with a column "seq" int set to Identity Increment to 1. It is my "main" table for records others lookup warehouse info from. I need to update/insert records automatically every morning from another table. I'm using SSIS to create the package. I cannot, anyway I try, insert records into the table without first deleting the "seq" column which is crazy. I have tried setting "set identity_insert mytable on"; doesn't work. Does anyone have the answer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:50:21

why? whats the error you're getting when you try to insert? ALso is seq pk of table? If you want to update values of column why keeping it as identity?

nshovater
Starting Member

6 Posts

Posted - 2009-01-08 : 12:36:59

quote:
Originally posted by visakh16

why? whats the error you're getting when you try to insert? ALso is seq pk of table? If you want to update values of column why keeping it as identity?

For example, I can try to run this:
BEGIN TRAN DEDUPLICATE
--
--SET IDENTITY_INSERT tbl_InventoryList ON

SELECT DISTINCT
*
INTO #tempStg
FROM tbl_InventoryList

TRUNCATE TABLE tbl_InventoryList

INSERT INTO tbl_InventoryList
SELECT
*
FROM #tempStg

SELECT * FROM tbl_InventoryList

DROP TABLE #tempStg

--SET IDENTITY_INSERT tbl_InventoryList OFF

COMMIT TRAN DEDUPLICATE

With the SET IDENTITY_INSERT ON OR OFF the error I get is:
Msg 8101, Level 16, State 1, Line 12
An explicit value for the identity column in table 'tbl_InventoryList' can only be specified when a column list is used and IDENTITY_INSERT is ON.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 12:51:24

it clearly suggests the problem. you should have SET IDENTITY_INSERT tbl_InventoryList ON and also insert statement should be


....
INSERT INTO tbl_InventoryList (IDCol,col1,col2,...
SELECT IDCol,Col1,Col2,..
FROM #tempStg

.....

nshovater
Starting Member

6 Posts

Posted - 2009-01-08 : 15:37:04

quote:
Originally posted by visakh16

it clearly suggests the problem. you should have SET IDENTITY_INSERT tbl_InventoryList ON and also insert statement should be

....
INSERT INTO tbl_InventoryList (IDCol,col1,col2,...
SELECT IDCol,Col1,Col2,..
FROM #tempStg

.....

I tried with SET IDENTITY_INSERT tbl_InventoryList ON
many times (I know in this example it is commented out) but I have tried it. Does the columns have to be written out?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:38:31

quote:
Originally posted by nshovater

quote:
Originally posted by visakh16

it clearly suggests the problem. you should have SET IDENTITY_INSERT tbl_InventoryList ON and also insert statement should be

....
INSERT INTO tbl_InventoryList (IDCol,col1,col2,...
SELECT IDCol,Col1,Col2,..
FROM #tempStg

.....

I tried with SET IDENTITY_INSERT tbl_InventoryList ON
many times (I know in this example it is commented out) but I have tried it. Does the columns have to be written out?

Yup it needs to as i've shown above

nshovater
Starting Member

6 Posts

Posted - 2009-01-12 : 13:56:13

quote:
Originally posted by visakh16

quote:
Originally posted by nshovater

quote:
Originally posted by visakh16

it clearly suggests the problem. you should have SET IDENTITY_INSERT tbl_InventoryList ON and also insert statement should be

....
INSERT INTO tbl_InventoryList (IDCol,col1,col2,...
SELECT IDCol,Col1,Col2,..
FROM #tempStg

.....

I tried with SET IDENTITY_INSERT tbl_InventoryList ON
many times (I know in this example it is commented out) but I have tried it. Does the columns have to be written out?

Yup it needs to as i've shown above

THANKS FOR YOUR HELP!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources