Table insert from Access/Excel

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

General SQL Server Forums

New to SQL Server Administration

Table insert from Access/Excel

Author

Topic

kizofilax
Starting Member

1 Post

Posted - 2012-09-25 : 13:49:58

Hello, I have a table in SQL server and I log into it using SQL server management studio. The original information comes from Excel files in a sharepoint so what I started doing was:

Excel file ---> Access DB ---> SQL Server (once I got access to it)

The excel files are released every week and so the table needs to be updated. In Access it is easy because I set a primary key and then Access just updates the rows that don't have key violations

I tried the same thing in SQL server but it didn't like it. It gave me several errors. In SQL Server Management Studio I do:
- Import data
- Chose my Access DB and import data to the same table with same name in SQL
- Before I hit "finish" I read "The new rows will be appended to the existing table" so I assumed this would work as in Access

And I get the following errors

Copying to [dbo].[ISS-Historical-Outages] (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 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_ISS-Historical-Outages'. Cannot insert duplicate key in object 'dbo.ISS-Historical-Outages'.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - ISS-Historical-Outages.Inputs[Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Destination - ISS-Historical-Outages.Inputs[Destination Input]" 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 - ISS-Historical-Outages" (223) failed with error code 0xC0209029 while processing input "Destination Input" (236). 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)

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - ISS-Historical-Outages returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Any idea on how to do this??

Thanks

komkrit
Yak Posting Veteran

60 Posts

Posted - 2012-09-26 : 00:13:47

In SQL Server Import Wizard, if you choose to "Append rows to the destination table"
It will copy all records of source table then put them to SQL Server table.
There is no differential data import in SQL Server Import Wizard.

In this case, it is easy trick to import data.
You just need to choose option "Delete rows in destination table"
It will delete all rows in destination table first, then copy all rows from source table and insert here.
If your destination table has identity column(auto increment), you need to choose additional option "Enable identity insert"
It will place identity value as same value as the source table. No auto increment of identity ciolumn at destination table during import.

Good Luck

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Welcome for all questions. Let us know if our solution solved your problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources