Importing Data from Access 2003 table to SQL 2005 table 1

[larrydavid]

Hello, I have an Access 2003 database with one table with approx 45K records I need to export into an existing SQL Server 2005 table that has approx 200K records. Everything seems pretty straightforward, but the part I'm having trouble with is that some of the fields will need some logic behind how they will be mapped to SQL Server. I'm thinking some CASE When/Then logic will be needed, so I would like to do this in a stored procedure. However, everything I'm seeing online points to just using the DTS import/export wizard. I would prefer a stored procedure, but I guess I could do the export from an Access VBA module also. In any case, I will be concatenating some fields into one and doing some routing logic (via If/Then or CASE When/Then). So, can someone suggest the best approach and perhaps point me to a good example? I definitely don't want to export from the QBE grid in Access because the IF/THEN logic would be harder to implement.

Thanks,
Larry

 

[simian336]

Use DTS to import them to a temp table then create you SP to break them out as you see fit.

Simi

 

[larrydavid]

Thank you Simi. Kind of what I was thinking but you confirmed it.

Regards,
Larry

 

[sparkbyte]

With SQL 2005, I would use SSIS.



Thanks

John Fuhrman