transferring data from sybase to ms sql 2005

[NickyJay]

Hi,

I have an organisation table in a sybase db (that I am not allowed to connect to via odbc)and so getting new/changed data out ok via dbisql nightly into .txt file.

The contents of this .txt file then needs to be put into a local sql2005 db. the original data went in no probs via bcp (as all new primary key info) but can't?? use this to then further update/append changes from sybase db into 2005 db (please advise if there is a switch that does append/update).

So.... tried to create an import job within 2005 to take the .txt and read it in. Fails on pk violation where the data is simple amends. There doesn't appear to be an option to append/update - only append or delete.

Does anyone know whether an "update" can be done and how to do it via a scheduled job?

Thanks.

 

[SQLSister]

Suggest you check out SSIS.

Also you could put the data into a work table and then run ordinary SQL commands in a stored proc to update old records and insert new ones on the real tables.

"NOTHING is more important in a database than integrity." ESquared

 

[NickyJay]

I was doing it via a SSIS package but there are only radio button options to "delete all rows" "append data" - there's no append/update option ;0(

I'll try to use the temp table system as you suggest - thanks!

Nic