How do I Import table from an ODBC Database ?

Question

I have an ODBC Database (some third party DB) where I have a bunch of tables. I am able to use 'GetSchema' to get a list of tables in there. I am also abe to utilize SQLBulkCopy to copy Data from these tables to SQL Server tables (only when I have created a Destination table with the same structure in SQL Server).

But here is the scenario - an update was applied to the ODBC database and now it has a new table (I am able to figure that out via comparing Old & new list of tables in ODBC database.

Is there a way to either Import the entire table (empty or with Data) OR Generate SQL script for the Source table so that Destination table can be created before using Bulk Copy operation ?

thanks RD

Answer 1

Why don't you use the import/export wizard? You can access it by right-clicking on a database in SSMS, and selecting Tasks > Import Data.

On the "Select Source Tables and Views" page, click "Edit Mappings" and "Edit SQL", if you only want the CREATE TABLE SQL, otherwise you can use the wizard to perform the import as well.

Answer 2

using Import feature of SQL Server would require DSN to exist on the same machine as SQL Server. In this scenario, I have ODBC database on a different machine. Also i need to automate this so only .NET solution is feasible in this scenario. I can install .NET solution on this machine & connect to SQL Server via .NET SQL Connection providers.

thanks Rahul