Can SqlBulkCopy create a table from a Sql selection

sqlbulkcopy

Question

Can SqlBulkCopy create a table, kind of like a SELECT INTO?

Accepted Answer

It seems that SqlBulkCopy can not create tables by itself. The destination table has to be predefined. In the case where the destination has got an auto incremental identity (int), just use a 1 in the select statement i.e.

SELECT 
    1, 
    [ColumnName],
    [ColumnName]...
FROM TABLENAME

SQL Server will handle the auto increment by itself.


Popular Answer

I think answer above wasn't quite clear.

You must create table with SQL. There is no other way. And if you need just to create column structure, then it is quite simple if your source is in the same server, it is enough to do this:

Select * from source_table into destination_table where 1=2

If your source is not in same server (e.g. it is excel or dbf file or whatever), the easiest thing to do is to connect to it with ODBC (or SQL if possible), and send him:

    Select * from source_table where 1=2

and then collect result into DataTable. Then in second step you should create stored procedure on your destination server that will take that table as argument and then insert it into new table.

A bit more precisely, try this for SQL procedure: http://www.builderau.com.au/program/sqlserver/soa/Passing-table-valued-parameters-in-SQL-Server-2008/0,339028455,339282577,00.htm

And create SqlCommnand object in c# and add to its Parameters collection SqlParameter that is SqlDbType.Structured

I didn't go into every single detail, but hope that it can help.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why