Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Do Not Call list Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 2:42 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 3:36 PM
Points: 431, Visits: 1,744
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 )

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

Post #1438578
Posted Wednesday, April 3, 2013 2:45 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 3:36 PM
Points: 431, Visits: 1,744
USE [blah]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[DeDupe]
@projectID int,
@dupVar1 varchar(20) ='',
@dupVar2 varchar(20) ='',
@dupVar3 varchar(20) =''
as

DECLARE @ProcessTable varchar(64)
select @ProcessTable=ds_ProcessTable from blah.dbo.ds_dataset where ds_ProjectID=@ProjectID
declare @SQLString varchar(1000)

set @sqlString='with d1 as (
select id, [var1]
from [table]
),
d2 as (
select id, [var1]
from [table]
)
update [table]
set statusflag = 10 where id in (
select d1.id
from d1, d2
where d1.id > d2.id
and d1.[var1] = d2.[var1]
) '

if @dupvar2 <> ''
begin
SET @SQLString='with d1 as (
select id, [var1], [var2]
from [table]
),
d2 as (
select id, [var1], [var2]
from [table]
)
update [table]
set statusflag = 10 where id in (
select d1.id
from d1, d2
where d1.id > d2.id
and d1.[var1] = d2.[var1]
and d1.[var2] = d2.[var2]
) '
SET @SQLString=REPLACE(@SQLString,'[var2]',@dupVar2)
end

if @dupvar3 <> ''
begin
SET @SQLString='with d1 as (
select id, [var1], [var2], [var3]
from [table]
),
d2 as (
select id, [var1], [var2], [var3]
from [table]
)
update [table]
set statusflag = 10 where id in (
select d1.id
from d1, d2
where d1.id > d2.id
and d1.[var1] = d2.[var1]
and d1.[var2] = d2.[var2]
and d1.[var3] = d2.[var3]
) '
SET @SQLString=REPLACE(@SQLString,'[var2]',@dupVar2)
SET @SQLString=REPLACE(@SQLString,'[var3]',@dupVar3)
end


SET @SQLString=REPLACE(@SQLString,'[table]',@ProcessTable)
SET @SQLString=REPLACE(@SQLString,'[var1]',@dupVar1)

exec (@SQLString)

Post #1438580
Posted Wednesday, April 3, 2013 2:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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 )

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



From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438581
Posted Wednesday, April 3, 2013 2:54 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 3:36 PM
Points: 431, Visits: 1,744
Lynn Pettis (4/3/2013)


From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.


There is no space.
Post #1438584
Posted Wednesday, April 3, 2013 2:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
erikd (4/3/2013)
Lynn Pettis (4/3/2013)


From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.


There is no space.


So, does this mean you are using the \ n (no space between the \ and the n)?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438586
Posted Wednesday, April 3, 2013 2:59 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 3:36 PM
Points: 431, Visits: 1,744
Lynn Pettis (4/3/2013)
erikd (4/3/2013)
Lynn Pettis (4/3/2013)


From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.


There is no space.


So, does this mean you are using the \ n (no space between the \ and the n)?


Yes ma'am. No spaces at all.

Post #1438587
Posted Wednesday, April 3, 2013 3:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
erikd (4/3/2013)
Lynn Pettis (4/3/2013)
erikd (4/3/2013)
Lynn Pettis (4/3/2013)


From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.


There is no space.


So, does this mean you are using the \ n (no space between the \ and the n)?


Yes ma'am. No spaces at all.



First, sir. Second that's my dad.

Have you tried \ r \ n (again with no spaces between the \ and the r and n).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438591
Posted Wednesday, April 3, 2013 3:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 13,471, Visits: 12,329
erikd (4/3/2013)
Lynn Pettis (4/3/2013)
erikd (4/3/2013)
Lynn Pettis (4/3/2013)


From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.


There is no space.


So, does this mean you are using the \ n (no space between the \ and the n)?


Yes ma'am. No spaces at all.



You might modify your response slightly. Lynn is not a ma'am.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438592
Posted Wednesday, April 3, 2013 3:23 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 3:36 PM
Points: 431, Visits: 1,744
Lynn Pettis (4/3/2013)
erikd (4/3/2013)
Lynn Pettis (4/3/2013)
erikd (4/3/2013)
Lynn Pettis (4/3/2013)


From your post I can't tell if you are using '\ n' (no space between the \ and n) as your row terminator.


There is no space.


So, does this mean you are using the \ n (no space between the \ and the n)?


Yes ma'am. No spaces at all.



First, sir. Second that's my dad.

Have you tried \ r \ n (again with no spaces between the \ and the r and n).


Hm. I didn't know it could do that.

Now that my face has been properly de-egged:

Yes, with this error message:

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 2. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

This is with the phonenumber field set to varchar(7), as it should be in the file.

Post #1438597
Posted Wednesday, April 3, 2013 3:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
Can you upload a 10 line snippet of the file as a .txt file, please?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438598
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse