xoxomos
asked on
Handling unwanted records during bcp
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?
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.
TSM1999
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.
TSM1999
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
Thank you,
Moondancer
Moderator @ Experts Exchange
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.