Bulk 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)

Bulk Insert Problem

Author

Topic

srinivas.alwala
Starting Member

30 Posts

Posted - 2008-04-04 : 09:09:01

Hi,

currently the table has an identity column with seed,increment as 1,1.
The data in table being deleted using Truncate statement.

Now, I need to insert data (Bulk Insert) from a table in which the same table structure being used.

I recd the foll. error.

Msg 8101, Level 16, State 1, Line 3
An explicit value for the identity column in table 'PBAL04SQL01.DMIS_R&D.dbo.Employee_History' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Kindly let me know how to rectify this error.

Regards,
Srinivas Alwala

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 11:48:44

the error message clearly suggests what to do. just set identity insert

SET IDENTITY_INSERT ON

INSERT INTO Destination
SELECT * FROM Source

SET IDENTITY_INSERT OFF

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-04 : 12:05:05

Also don't forget to include column list in your INSERT statement.

SET IDENTITY_INSERT ON

INSERT INTO Destination(col1, col2,..)
SELECT col1, col2,.. FROM Source

SET IDENTITY_INSERT OFF

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

srinivas.alwala
Starting Member

30 Posts

Posted - 2008-04-23 : 06:36:15

Hi,

In destination table an identity being already set as identity(1,1)

The data in source table starting with value 60 for the same identity column.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-23 : 07:01:40

[code]SET IDENTITY_INSERT Destination ON

INSERT INTO Destination(col1, col2,..)
SELECT col1, col2,.. FROM Source

SET IDENTITY_INSERT Destination OFF[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources