Date: 06/15/05 (SQL Server)    Keywords: sql

Okay, so I'm pulling a text file into SQL Server 2000 using an ActiveX script. Script works flawlessly on a number of other files. It shunts records to various tables depending on markers in the data itself, splitting the data, assigning record numbers, and then using an INSERT INTO statement to actually get the data into the tables. A typical (short) INSERT INTO statement looks like this:

DestSQL = "INSERT INTO amt VALUES (" & recordnum & "," & subrecordnum & ",'" & _
sArray(0) & "','" & sArray(1) & "','" & sArray(2) & "','" & sArray(3) & "')"

The data from the text file is all character data (and it really IS character data, i.e., I can't convert it all into numerics or what have you), thus the need for the single quotes.


...and now, the dilemma. A new client just sent me a test file to run through to make sure we can process it. It bombed. I dug through the data for half an hour or so trying to figure out why before realizing that the data contains... apostrophes! And thus, the INSERT INTO statement comes out looking like...

INSERT INTO n3 VALUES (1,23,'MONTAN'S BAY','OH','44121','')"

You can see the problem immediately. (Which is better than I can say for me. sheesh.)

Here's the monkeywrench: while I was only tasked with this this morning, we'll start getting regular cuts from the client in July. No idea when in July yet, so I'm figuring a worst-case scenario of July 5.

Here's the spanner attached to the monkeywrench: for various reasons, I have various vacation days planned between now and July 5 that I can't get out of. I have eight and a half days to have this up and running. Along with all the other stuff I gotta do. So rewriting the package from scratch is probably out.

I'm thinking of going through each line and stripping out the apostrophes, but I'm sure everyone involved would rather have the data look the same way coming out of the package that it went into the package (except stuffed into tables).

Anyone know of a quick and dirty way to get apostrophes past an INSERT INTO statement? Thanks...

Source: http://www.livejournal.com/community/sqlserver/28770.html