CODEWITHSUNDEEP
sql-serversql-server-2008-r2bulkinsert
Karl
Karl

Reputation: 5822

SQL BULK INSERT with on the fly table creation

I would like to use BULK INSERT to load a few hundred raw data tables into SQL Server. The format of these tables would be similar, although not identical (they come from excel sheets which are not tightly version controlled).

I want to know if there is a way to dynamically generate the table required on SQL Server depending on the headers in the file to be loaded, and then do the BULK INSERT thereafter.

Upvotes: 1

Views: 5459

Answers (2)

usr
usr

Reputation: 171236

You can connect to those Excel tables using OPEN ROWSET. Then, do the following:

SELECT *
FROM Excel
INTO NewTable
WHERE 0=1

This will transfer the schema. Is this what you want?

Upvotes: 1

bako_mite
bako_mite

Reputation: 24

You can make an excel script that generates sql statements for creation of the tables, then execute the file before bulk insert the data.

Upvotes: 0

Related Questions