Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Handling unwanted records during bcp

Posted on 1999-10-02
Medium Priority
Last Modified: 2008-03-06
I get a file which i will attempt to bcp into a table.  I got an error because there is a unique index.  I know it would probably be better to drop the index before copying, but i still want to know about the record(s) that are violating my integrity.  Do i need to load the table first, then do something like selecting a distinct or unique into another table or is there a way to check during the bcp copy process, identify the offending records and continue copying?
Question by:xoxomos
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 2093833
I don't know of a way to check in the bcp process. What I've done, in the past, is drop unique indexes, bcp the data in, check the fields for uniqueness and fix any problems, then re-index the table.

You can use the following select statement to identify any duplicate keys that might exist in your table (assuming the key is a single field):

Select keyfield, count(*) from table group by keyfield

Any field that has a count > 1 is suspect and needs to be investigated.

Expert Comment

ID: 2094543
BCP is just a Bulk Copy, you don't get any control on individual records that get inserted...

For one of my project I had to do such a thing (importing dayly data from COBOL text files) I used temporary tables and a stored procedure to insert data from the temp table to 'normalize' data.

If you're using MSSQL V7, you might have a look at the Data Transformation Services, this is a nice feature that's more integrated in MSSQL and gives you more control on the whole process...

Hope this helps.


Accepted Solution

JHausmann earned 200 total points
ID: 2097004
Being new to this, I assume this is the question I answered. If not, sorry.

Expert Comment

ID: 6859247
This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.
Thank you,
Moderator @ Experts Exchange

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question