SQL import data when both source and destination has data and identity column

Question

SQL import data when both source and destination has data and identity column

IT researcher

SSCertifiable

Points: 7608
More actions
September 8, 2014 at 7:14 am

#397314

I am using Import and export data option to import data from one database to another database. Both database have similar structure of tables. All the tables have primary key which is identity element(auto incremented by 1) .Both source and destination tables have rows.
Now when I import data I get error like Failure
inserting into the read-only column
So I enabled enable
identity insert check-box and tried to import data. In this case I got error as shown below
Copying to [dbo].[Misc_Data] (Error)
Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_Misc_Data'. Cannot insert duplicate key in object 'dbo.Misc_Data'. The duplicate key value is (1).".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (50)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (50)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - Misc_Data" (37) failed with error code 0xC0209029 while processing input "Destination Input" (50). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
I think when i enable identity insert on SQL try to insert rows with values same as in souce table for identity column and gives error.
So how can I import data in this case. When having data in both source and destination and identity column is set as primary key.

Viewing 11 posts - 1 through 10 (of 10 total)

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

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

Can you just not import that column? That should let the identity column on the destination table work like it's supposed to.

IT researcher SSCertifiable Points: 7608 More actions · Answer 2

ZZartin (9/8/2014)
Can you just not import that column? That should let the identity column on the destination table work like it's supposed to.

How can i import data without one particular column and does it works?

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 3

If you're using the import export wizard in Management studio when you are running the wizard when you are selecting the column mappings(after selecting the source and destination) on the Select Source Tables and Views screen hit the edit mappings button, find the identity column and for the destination select <ignore>

Super Cat SSCertifiable Points: 7334 More actions · Answer 4

Do it via T-sql and set identity insert on

-- SET IDENTITY_INSERT to ON.

SET IDENTITY_INSERT products ON

GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

IT researcher (9/8/2014)
I am using Import and export data option to import data from one database to another database. Both database have similar structure of tables. All the tables have primary key which is identity element(auto incremented by 1) .Both source and destination tables have rows.

Are the two databases on the same instance?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

x SSC-Insane Points: 23660 More actions · Answer 6

Talib123 (9/8/2014)
Do it via T-sql and set identity insert on
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

It really sounds like he tried this, but in the destination table the value already existed. Its an identity column that is also a key, so can't receive a row with the duplicate value, ie., that value already exists in the destination column.

The path he needs to take depends on the meaning of this identity column.

IT researcher SSCertifiable Points: 7608 More actions · Answer 7

patrickmcginnis59 10839 (9/8/2014)
Talib123 (9/8/2014)
Do it via T-sql and set identity insert on
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
It really sounds like he tried this, but in the destination table the value already existed. Its an identity column that is also a key, so can't receive a row with the duplicate value, ie., that value already exists in the destination column.
The path he needs to take depends on the meaning of this identity column.

Yes. But there is no foreign key associate with this column . so i can just use ignore option for that row in edit mappings.It works fine.

But i wanted to know whether i can use write a query option and exclude that row. does it work same?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

IT researcher (9/9/2014)
patrickmcginnis59 10839 (9/8/2014)
Talib123 (9/8/2014)
Do it via T-sql and set identity insert on
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO
It really sounds like he tried this, but in the destination table the value already existed. Its an identity column that is also a key, so can't receive a row with the duplicate value, ie., that value already exists in the destination column.
The path he needs to take depends on the meaning of this identity column.
Yes. But there is no foreign key associate with this column . so i can just use ignore option for that row in edit mappings.It works fine.
But i wanted to know whether i can use write a query option and exclude that row. does it work same?

Are the two db's on the same or different instances?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

IT researcher SSCertifiable Points: 7608 More actions · Answer 9

ChrisM@Work (9/8/2014)
IT researcher (9/8/2014)
I am using Import and export data option to import data from one database to another database. Both database have similar structure of tables. All the tables have primary key which is identity element(auto incremented by 1) .Both source and destination tables have rows.
Are the two databases on the same instance?

They are in different instances.

Anser by ZZartin to use <ignore> in mapping for those identity column works properly.

It works perfectly. but i want to know how it can be done using specifying query. I just gave select query by excluding identity column in option Write a query to specify the data to transfer option. But no rows are imported. Is there anything to do in edit mappings if i specify query or does those column map automatically?

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 10

IT researcher (9/9/2014)
ChrisM@Work (9/8/2014)
IT researcher (9/8/2014)
I am using Import and export data option to import data from one database to another database. Both database have similar structure of tables. All the tables have primary key which is identity element(auto incremented by 1) .Both source and destination tables have rows.
Are the two databases on the same instance?
They are in different instances.
Anser by ZZartin to use <ignore> in mapping for those identity column works properly.
It works perfectly. but i want to know how it can be done using specifying query. I just gave select query by excluding identity column in option Write a query to specify the data to transfer option. But no rows are imported. Is there anything to do in edit mappings if i specify query or does those column map automatically?

If you use specify query in the wizard you would do it the same way, in the edit mappings screens you would specify destination columns for all the source columns with nothing mapped to the identity field.