using bulk insert with the into command

Question

Post reply

using bulk insert with the into command

bryan van ritter

SSCrazy

Points: 2270
More actions
November 25, 2010 at 5:58 am

#141564

Hi,
i want to know if it is possible to use the bulk insert command with the into command.
i want to insert into a table without to create the table for the first time.
thanx in advance,
bryan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

it depends on the source.

any SQL statement that features a FROM Clause can optionally have an INTO NEWTABLENAME in it.

so the trick is to wrap BULK INSERT so it has that FROM Clause.

the issue is the column names...bulk insert doesn't know the column names, so it cannot create a table on the fly.

but if you wrap bulk insert with openrowset, or use open rowset directly, then you might be able to.

here's two examples from my snippets;:

this one , while not technically using bulk insert, can do what you are asking.

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Text;Database=C:\Files\CSV;HDR=YES;FMT=Delimited',

'SELECT * FROM example.csv')

my second example, harvested from a post by Paul White, can do what you are asking, because an xml format file was created to define the columns. that extra step allows you to do what you are asking...but the effort to make a format file is probably more than that to create a table in SQL:

Create an XML format file like so:

<?xml version="1.0" ?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="01" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="02" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="03" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="04" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="05" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="06" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="07" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="08" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="09" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50"/>

<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="50"/>

</RECORD>

<ROW>

<COLUMN SOURCE="01" NAME="col1_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="02" NAME="col2_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="03" NAME="col3_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="04" NAME="col4_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="05" NAME="col5_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="06" NAME="col6_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="07" NAME="col7_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="08" NAME="col8_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="09" NAME="col9_dirty" xsi:type="SQLVARYCHAR" />

<COLUMN SOURCE="10" NAME="col10_dirty" xsi:type="SQLVARYCHAR" />

</ROW>

</BCPFORMAT>

And use the BULK OPENROWSET provider to allow you to pre-process the raw data before inserting into the destination table:

INSERT dbo.zz_u_test_TEMP WITH (TABLOCK)

(

col1_dirty, col2_dirty, col3_dirty, col4_dirty, col5_dirty,

col6_dirty, col7_dirty, col8_dirty, col9_dirty, col10_dirty

)

SELECT REPLACE(RowSource.col1_dirty, CHAR(34), SPACE(0)),

REPLACE(RowSource.col2_dirty, CHAR(34), SPACE(0)),

REPLACE(RowSource.col3_dirty, CHAR(34), SPACE(0)),

REPLACE(RowSource.col4_dirty, CHAR(34), SPACE(0)),

REPLACE(RowSource.col5_dirty, CHAR(34), SPACE(0)),

REPLACE(RowSource.col6_dirty, CHAR(34), SPACE(0)),

RowSource.col7_dirty,

RowSource.col8_dirty,

RowSource.col9_dirty,

RowSource.col10_dirty

FROM OPENROWSET

(

BULK 'C:\Documents and Settings\Paul\Test.data',

FORMATFILE = 'C:\Documents and Settings\Paul\format.xml',

CODEPAGE = 'RAW',

FIRSTROW = 0,

LASTROW = 0,

MAXERRORS = 0,

ROWS_PER_BATCH = 0

) RowSource;

Paul White (I think)
This preserves all the benefits of minimally-logged fast bulk load, while giving you the opportunity to apply transformations in the SELECT clause. Very cool.
The data loaded using the simple example above is:
col1_dirty col2_dirty col3_dirty col4_dirty col5_dirty col6_dirty col7_dirty col8_dirty col9_dirty col10_dirty
1 7 D 2 _ NULL 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bryan van ritter SSCrazy Points: 2270 More actions · Answer 2

Thanks for the quick answer.

now i've tried this but i get the following error:

Msg 7403, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

This is maybe because i have a sql 2005 64 bits running on my machine.

is there a way to bypass this. the file resides on the database server.

so a local import of the file.

kind regards,

bryan