Need to dump a large amount of data into sql server? Have I got some tools for you! Recently I needed to, from a stored procedure, copy a large amount of data from a text file on the disk into my sql server. This is nice an easy with SQL Server 2005. You can write a simple INSERT INTO statement that will copy the data out of the flat file into your table. Instead of providing a table name in the from part of your insert just use the OPENROWSET option with the BULK keyword and poof your data is in the database. The only trick is to creating the XML format file that describes to SQL Server what the text file looks like. But that is much simpler if you use the bcp utility with the format option to generate it (look about halfway down the page on the third link below). Happy importing!
Here is an example of the SQL: (easily embeddable in any stored procedure)
INSERT INTO destination_table
SELECT
column1,
column2,
column3,
column4
FROM OPENROWSET(BULK '\\server\folder\source.txt',
FORMATFILE='\\server\folder\format.xml'
) as t1 ;
Here is an example of the XML format file:
<?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="1" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="2" xsi:type="CharFixed" LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="3" xsi:type="CharFixed" LENGTH="32" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="4" xsi:type="CharTerm" MAX_LENGTH="60" TERMINATOR="\r\n" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="column1" xsi:type="SQLINT" />
<COLUMN SOURCE="2" NAME="column2" xsi:type="SQLVARYCHAR" LENGTH = "30" />
<COLUMN SOURCE="3" NAME="column3" xsi:type="SQLVARYCHAR" LENGTH = "32" />
<COLUMN SOURCE="4" NAME="column4" xsi:type="SQLVARYCHAR" LENGTH = "60" />
</ROW>
</BCPFORMAT>
Useful links:
OPENROWSET (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms190312.aspx
Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...)
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
Using a Format File to Bulk Import Data
http://msdn2.microsoft.com/en-us/library/ms178129.aspx