SQL Bulk Insert

So I’m sure there are a ton of resources already out there regarding this, so this is more for me.

To use the BULK INSERT command, you’ll first need to create your data file.

ID|StateID|ZipCode|City|County|State|TimeZone|PopulationPercent|FIPS
1|33|00501|HOLTSVILLE|Suffolk|NY|EST|100|36103
2|33|00501|I R S SERVICE CENTER|Suffolk|NY|EST|100|36103
3|33|00544|HOLTSVILLE|Suffolk|NY|EST|100|36103
4|33|00544|IRS SERVICE CENTER|Suffolk|NY|EST|100|36103
5|58|00601|ADJUNTAS|Adjuntas|PR||100|43001
...

Then you’ll need to create the SQL statement.

BULK INSERT [Table]
FROM 'c:\Dir\File.txt'
WITH (
     DATAFILETYPE = 'char',
     FIELDTERMINATOR = '|',
     FIRE_TRIGGERS,
     MAXERRORS = 50,
     TABLOCK
     )

And there you go!

Much faster than a crap-ton of insert statements 🙂

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.

Twitter 

Comments are closed.