Inserting data into existing table with Identity

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 2012 Forums

SSIS and Import/Export (2012)

Inserting data into existing table with Identity

Author

Topic

jduehmig
Starting Member

2 Posts

Posted - 2014-03-05 : 09:52:24

I used the SMAA to upsize an Access 2010 database to SQL Server 2005.

During the process a number of records were not imported into SQL Server due to some corrupt or illegal data. I have since cleaned up the data that was not imported and saved it to a temporary table in the database. I now want to insert that data into the original table. I have two issues that are preventing this from working. First, one of the fields, called Task_ID, is an auto-incrementing field. When I ran a standard insert query, the resulting data auto-incremented and did not use the imported Task_ID value. I need a way to get this data into the field without it being changed. Also, my temp table contains some duplicate records from the production table.

The second issue is the Primary Key, which is also an Identity Field. I've tried using the SET IDENTITY_INSERT ON command but no luck. Here is my query:

USE "R&D Tasks"
SET IDENTITY_INSERT TaskList ON
INSERT INTO TaskList (TAskID, NPD#, Submitted_By, Date_Submitted, Priority, Status, Due, Subject, Description, Date_Completed, Completed_By, TimeEstimate)
SELECT TAskID, NPD#, Submitted_By, Date_Submitted, Priority, Status, Due, Subject, Description, Date_Completed, Completed_By, TimeEstimate
FROM TaskList_Temp
SET IDENTITY_INSERT TaskList OFF

The error I get is:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Task'.

The column 'Task' is the PK. If I don't include that column in my query I get the following:
Msg 545, Level 16, State 1, Line 3
Explicit value must be specified for identity column in table 'TaskList' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Replication is not enabled on this database so that isn't the problem. Can anyone point me in the right direction on this?
Thanks,
Joe

jduehmig
Starting Member

2 Posts

Posted - 2014-03-05 : 10:06:43

Sorry, I included the wrong query in my OP. Here is the correct one:

USE "R&D Tasks"
SET IDENTITY_INSERT TaskList ON
INSERT INTO TaskList (Task, TaskID, NPD#, Submitted_By, Date_Submitted, Priority, Status, Due, Subject, Description, Date_Completed, Completed_By, TimeEstimate)
SELECT Task, TaskID, NPD#, Submitted_By, Date_Submitted, Priority, Status, Due, Subject, Description, Date_Completed, Completed_By, TimeEstimate
FROM TaskList_Temp
SET IDENTITY_INSERT TaskList OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources