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

Question

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.

EDIT The query I used to import table except one column(identity column) is shown below

SELECT [Name]
  ,[Status]
  ,[Last_Checked]
  ,[RecievedDate]
  ,[RecptNo]
  ,[Date2]
  ,[processedDate]
  ,[ind_num]
  ,[AcNo]
  ,[returnType]
  ,[sign]
  ,[oldStatus]
  ,[Acno_old]
  ,[Remarks]
  ,[FileName]
  ,[Status_checked]
  ,[Date_Time]
  ,[By]

FROM [dbname].[dbo].[mytable]

In this case i get error like

- Pre-execute (Error)
Messages
Error 0xc0202004: Data Flow Task 1: The number of columns is incorrect.

(SQL Server Import and Export Wizard)

Error 0xc0202025: Data Flow Task 1: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
 (SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task 1: component "Destination - Query" (73) failed the pre-execute phase and returned error code 0xC0202025.
(SQL Server Import and Export Wizard)

Both tables have same structure and same datatypes. Still i am getting error.

Answer 1

Don't just use the whole table. Instead choose the option to write a query.

Then simply write a select statement that doesn't use that Identity Column.

Alternatively, when you map columns, if you click "Delete rows in destination table", it will truncate the target table before loading data. If the opportunity exists for data to be removed from the source system and it should have remained in the destination, then this step will not be right for you.

Your package would look something like this