erikd (4/3/2013)

---

Hi,

So, even though my company has nothing to do with telemarketing, we've been asked to bump phone numbers for one project up against the national DNC list. It's a whopper. I'm running into some issues, and I was hoping someone here has either worked with it, or maybe has some pointers in working with a file this large (2.5gb text file :w00t:)

I guess first off, here's how I'm trying to load it, though the create table syntax isn't set in stone:

CREATE TABLE blah.dbo.nationaldnc

(area nvarchar (3) NOT NULL,

phonenumber varchar(8000) NOT NULL)

BULK INSERT blah.dbo.nationaldnc FROM '\\myserver\Sample\2013-4-3_Global_18B81869-3F4C-4C1F-BF46-FF33984D9900.txt'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '', BATCHSIZE = 100000)

This is how the file is set up, per donotcall.gov

The Full List in a Flat Text File has one three-digit area code, a comma, and a seven-digit telephone number per line, with a linefeed at the end of each line:

123,4567890

123,4567890

123,4567890

My problems are:

When I use anything other than nvarchar(max) for the second column, I get truncation errors. That makes me think that I'm using the wrong row terminator. However I'm not sure how to use char(10) for it, since just writing in char(10) doesn't seem to work.

The other will likely be when bumping the phone list up against it, how to write the least harsh query. I have stored procedures that do it with our internal do not contact list, but it's so much smaller than this that I'm not sure they'll scale. I'll post those second, as it's somewhat long.

Any ideas or suggestions would be welcomed.

Thanks