SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Inserting data into existing table with Identity
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jduehmig
Starting Member

2 Posts

Posted - 03/05/2014 :  09:52:24  Show Profile  Reply with Quote
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 - 03/05/2014 :  10:06:43  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000