June 14, 2013 at 8:40 am
Let's say I have a data file with last names:
SMITH
JONES
O'HARA (Note that this one has a single quote!)
CARTER
that I want to insert into a table:
create table last_names
(
name varchar(MAX)
)
I can write a stored procedure
create procedure sp_insert_last_name(@last_name varchar(MAX))
as
begin
insert into last_names(name) values (@last_name)
end
And some higher level code (maybe C, C#, whatever)
while (not end-of-data-file)
last_name = read(data-file)
last_name.replace("'","''",globally) // globally escape single quotes
call sp_insert_last_name(last_name)
end while
This works because I coded to escape the single quote.
What if I wanted to use BULK INSERT to do this? Will the single quote be handled correctly or will the last name O'HARA cause an error?
June 14, 2013 at 8:48 am
It handles embedded quotes without any problems.
When doing BULK INSERT, I like to use a format file to specify the data source and destination and then direct the BULK INSERT statement to point to the CSV file and use that format file. It works very well and is ultra consistent.
Depending on your table layout, the format file can take a while to set up, but I find it's worth it. Once you've got it done, you can run repeated imports consistently.
Example:
BULK INSERT table_name
FROM 'D:\Temp\TextFile.txt'
WITH (DATAFILETYPE = 'CHAR',
FIRSTROW = 2,
FORMATFILE = 'D:\Temp\format_file.xml',
MAXERRORS = 0);
June 14, 2013 at 8:51 am
I should have also mentioned that the throughput of the BULK INSERT cannot even be compared to a row-by-row approach. The BULK INSERT is so much faster that it'll surprise you at first and you'll wonder if it worked.
June 15, 2013 at 8:41 am
To augment what Ed mentioned and highlight the .net aspect of your post. There is q class that is part of the .net framework called SqlBulkCopy that is an interface to SQL Server's "bulk load APIs" available in .net the same way BULK INSERT is an interface to those same APIs from within T-SQL. SqlBulkCopy can load data directly from a .net DataTable in memory to a database table, which can be very handy.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply