Error Rows Identification - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Error Rows Identification

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-06-22 : 18:15:37

Inside a transaction, I have a insert statement some thing like this

INSERT INTO TBLA (col1,col2) -- col1 is a primary key
SELECT col1,col2 FROM TBLA

In this scenario, if there is a primary key voilation, I will get a error. But is there any way to find which row voilated the contsraint?

This is on Sql Server 2005

Thanks
Karunakaran

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 22:14:58

quote:
INSERT INTO TBLA (col1,col2) -- col1 is a primary key
SELECT col1,col2 FROM TBLA

Of-course you will get error. You are selecting from TBLA and inserting back into TBLA.

quote:
is there any way to find which row voilated the contsraint?

All of the rows in TBLA

KH
[spoiler]Time is always against us[/spoiler]

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-06-22 : 22:28:23

quote:
Originally posted by khtan
Of-course you will get error. You are selecting from TBLA and inserting back into TBLA.

Sorry, it should had been TBLB

INSERT INTO TBLA (col1,col2) -- col1 is a primary key
SELECT col1,col2 FROM TBLB

Sorry,If my question was not clear.
Basically I'm trying to see is there a way to identify which row triggered the pk contraint error. So that I can log that rowid in a log table or so for later analysis.

One option I was thinking was to do insert in a batch manner say for 100 rows or something, so that atleast I can narrow down to 100 rows. Since TBLA can have 100,000's of rows and TBLB can have 10,000 - 20,000 or so.

Thanks
Karunakaran

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-22 : 23:59:16

Yes. Just INNER JOIN these 2 tables

SELECT a.col1, a.col2
FROM   TBLA a INNER JOIN TBLB b
       ON a.col1 = b.col1

KH
[spoiler]Time is always against us[/spoiler]

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-06-23 : 00:12:03

I dont know why I didnt think of eliminating the duplicates first and load the rest of the data. :(

Thanks for pointing it out.

But we are also talking about an inner join between a table with millions of records (which keeps growing everyday) and another table with 10,000's of records(max could be like 100,000 at any point of time), both of them have pk defined, but how the performance will be?

Thanks
Karunakaran

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-23 : 01:16:35

This will only insert records not found in TBLA from TBLB

INSERT INTO TBLA (col1, col2)
SELECT b.col1, b.col2 
FROM   TBLB b LEFT JOIN TBLA a
ON     b.col1 = a.col1
WHERE  a.col1 IS NULL

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources