Can SqlBulkCopy create a table from a Sql selection

Question

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

I would like to move data from an Oracle database to a Sql server database, and would like to have the destination tables be created automatically, is this possible using SqlBulkCopy? — BK., Oct 21 '10 at 04:07

score 0 · Accepted Answer · edited May 20 '13 at 02:42

0

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.

edited May 20 '13 at 02:42

Undo

25,519
37
106
129

answered Oct 25 '10 at 08:51

BK.

365
4
13

What if the file is different each time, isn't there anyway it can create the table on the fly? – Si8 Jun 04 '14 at 20:05

score 0 · Answer 2 · answered Jan 14 '11 at 18:00

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.

Can SqlBulkCopy create a table from a Sql selection

2 Answers2