Select from a DB2 table and insert to a SQL table

Question

I feel like this might be a crazy question, and if anyone has a better idea of how to do it, by all means please let me know.

I have a .Net C# project at work that validates certain tables against the company standards they must adhere to. The tables can be on SQL Server or DB2. When a table is initially added to the program, I gather the metadata/information about the table using select statements off of either information_schema.columns for sql, or syscat.columns for db2. Once I get that data, I store it in a table (let's call it all_table_information) on sql server containing that same information for every table that is validated in the program.

If the table is a SQL table, I can run this query (of course restricting it to only the columns from the table I want):

insert into [all_table_information]
    (table_id, column_name, data_type, max_char_length)
select table_id, column_name, data_type, character_maximum_length
    from information_schema.columns 
    where ...restrict to needed table...

And then execute that in a SqlCommand. But if it is a DB2 table, I have to run this query (restricted again to the columns I need):

select tabschema, tabname, colname, typename, length 
from syscat.columns 
where ...restrict to needed table...

Then get a DataReader with the results and loop through it inserting each row using:

while (dr.Read())
{
   insert into [all_table_information]
   (table_id, column_name, data_type, max_char_length) values 
   (..."'" + dr["whatever"] + "', '" + ....)

   ...execute sql here...

} 
dr.Close();

Doing it this way works, but it is slow, so I was just wondering if there was anyway to do this in one statement? I know you could save the db2 table information to a data table as well, but can you run a query against a data table directly?

Thanks, Ryan

Answer 1

Have you considered using SSIS, rather than writing an app to do it?

Answer 2

You would do better to create your SqlCommand outside of the dr.Read() and use parameters. This will increase performance and security, and you won't have to worry about properly forming your syntax dynamically. Like this...

SqlCommand insertCommand = new SqlCommand(connection);

insertCommand.CommandText = @"
insert into [all_table_information]
(table_id, column_name, data_type, max_char_length)
values
(@table_id, @column_name, @data_type, @max_char_length)";

...create your parameters and add them here

insertCommand.Prepare(); //precompiles the query
while (dr.Read())
{
    ...set parameter values

    insertCommand.ExecuteNonQuery();
} 
dr.Close();

Answer 3

You probably want to use SqlBulkCopy: MSDN

It is much faster than doing individual inserts for each row.