Solution on inserting good records or bad records

Question

Solution on inserting good records or bad records

Krasavita

SSC-Insane

Points: 23138
More actions
April 23, 2009 at 10:01 am

#199734

I have 5 csv files that I need to insert to the table. They contain 375000 records in total. Is there solution if one of the records are bad put it in seperate table(Errors) and continue putting good records to the table. Thank you

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

yulichka (4/23/2009)
I have 5 csv files that I need to insert to the table. They contain 375000 records in total. Is there solution if one of the records are bad put it in seperate table(Errors) and continue putting good records to the table. Thank you

Use SSIS and set up an Error flow task to put the bad records into a table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 2

This seems like the ideal task to use SSIS for (if you are familiar and comfortable with the tool). Obviously you will have some criteria that will tell you a good record from a bad record (whether that be a field in the file or a lookup done on something in the file, etc..) and SSIS gives you the ability to send those rows to different locations based on that criteria.

steveb. SSC-Forever Points: 46733 More actions · Answer 3

steveb.

SSC-Forever

Points: 46733

April 23, 2009 at 10:21 am

#983079

What criteria defines a bad record?

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

I am very sorry this is for 2000, all records are good,but for any reason if table won't take a record, I would need to insert to the table or text file

Steve Jones - SSC Editor SSC Guru Points: 738642 More actions · Answer 5

Moved to the 2000 forum.

What I'd do is insert all the records into a staging table. Then insert rows into the real table that are good, whatever that means.

You have to define what constitutes a good record or bad. If it's something like a PK violation, you could use bcp and send the errors out to a file, then process those records or remove them from the files..

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

steveb (4/23/2009)
What criteria defines a bad record?

Usually they are poorly dressed, nervous, don't look you in the eye and just run away when they see a DBA patrol car approaching 😛

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.