Populate table with suspect data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Populate table with suspect data

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-12 : 14:00:35

OK, I got 1.

I was getting a data from Oracle...

The dba spooled out 3 million records to a pipe(|) delimited file.

Not so bad...but he didn't SET PAGESIZE=0...basically turning off the headers...

so headers comeout intermitently through the file...DTS blows up...

Loaded trhe sucker in to a single column table...took awhile....

Now, ANY advice is greatly appreciate in getting the data in to its final destination...

I've identified all "good" data using predicates....

I'd like to build a flexible process that I can through any delimited data at and it'll loaded it from a single coumn table?

Anyone do this before?

Also, 3 million inserts? I'm thinking batches, right?

Brett

8-)

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-03-12 : 19:03:55

Just my advice, but have you considered removing the header / blank rows using someting like a PERL script?

It would be fairly easy to do and make your life on the SQL Server side much easier. Not sure if you will have to import this file in the same format (with headers) in the future - if so, I am guessing that a PERL scirpt would be much faster than anything SQL could do.

I have done a lot of web log manipulation (prior to import into SQL) and found PERL to be a the 'miracle drug'.

Another idea is that if you have already removed all the unwanted rows, what if you export all records to a text file, and then re-import using your pipe delimiter.

Hope this helps,

Jack

:)

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-03-12 : 19:39:45

Would running some SQL to eliminate dupes get rid of the rows that are the headers? I know we have lots of examples on how to get rid of dupes.

HTH,
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-15 : 08:57:12

Well, the miracle drug for me is sql....


SELECT TOP 10 *
FROM OPTION_STAGE
WHERE LEN(Col1)-LEN(REPLACE(Col1,'|','')) = 15
AND Col1 <> ' '
AND Col1 NOT LIKE 'EMPLID%'
AND Col1 NOT LIKE '------------%'
AND Col1 NOT LIKE 'SQL>%'
AND Col1 NOT LIKE '2805434%'

Gets me the sanitized data in pipe delimited format...

like..


                                                                                 
-------------------------------------------------------------------------------- 
100012|01|27|REG|01-JAN-02|01|2|G||||N|N|0|0|B                                  
100012|10|412|REG|01-JAN-02|02|462|W|||W|N|N|0|0|                               
100012|10|1924|REG|01-JAN-02|02|249|O|HMO152|1|259|N|N|0|0|B                    
100012|10|1925|REG|01-JAN-02|02|250|O|HMO152|2|260|N|N|0|0|B                    
100012|10|1926|REG|01-JAN-02|02|251|O|HMO152|3|261|N|N|0|0|B                    
100012|10|1927|REG|01-JAN-02|02|252|O|HMO152|4|262|N|N|0|0|B                    
100012|10|2056|REG|01-JAN-02|02|381|O|HMO193|1|391|N|N|0|0|B                    
100012|10|2057|REG|01-JAN-02|02|382|O|HMO193|2|392|N|N|0|0|B                    
100012|10|2058|REG|01-JAN-02|02|383|O|HMO193|3|393|N|N|0|0|B                    
100012|10|2059|REG|01-JAN-02|02|384|O|HMO193|4|394|N|N|0|0|B

Just need to figure out how to load it from a table...

I guess I could bcp it out and then back in...

Maybe that's the best solution as compared to doing inserts...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-18 : 12:07:19

Yup...bcp....what a gal!

3 million rows in a flash....so cool

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources