Looping vs. Set operation - question

Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran DjuranovicIn the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Hi JT,
Thanks for your response, but you didn't answer my question. I cannot join o
n IDs, because IDs from StaginTable (table variable in my case) are auto-inc
remented when populated from an XML file, so they don't necesserally match t
he IDs from ProductionTable.
Thanks
Goran
"JT" <someone@.microsoft.com> wrote in message news:ePL0zi8QGHA.3916@.TK2MSFTN
GP11.phx.gbl...
In the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||From your question, it sounded as if you have a situation where you are inse
rting from a staging table into a production table but the problem is that s
pecific rows cause the insert to fail. I was thinking perhaps the situation
is that the new row contains an ID that conflicts with the unique key constr
aint in the production table.
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:uKEm
7QHRGHA.3916@.TK2MSFTNGP11.phx.gbl...
Hi JT,
Thanks for your response, but you didn't answer my question. I cannot join o
n IDs, because IDs from StaginTable (table variable in my case) are auto-inc
remented when populated from an XML file, so they don't necesserally match t
he IDs from ProductionTable.
Thanks
Goran
"JT" <someone@.microsoft.com> wrote in message news:ePL0zi8QGHA.3916@.TK2MSFTN
GP11.phx.gbl...
In the example below, StagingTable contains the new rows to be inserted into
ProductionTable. Those rows from StagingTable that contain IDs already in u
se will be inserted into RejectTable. By joining Staging with Production, we
can avoid the use of a cursor or looping.
-- Insert rows into ProductionTable where the ID is not already in use.
insert into ProductionTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is null
-- Insert rows into RejectTable where the ID is already in use.
insert into RejectTable
select
*
from StagingTable as S
left join ProductionTable as P
on P.ID = S.ID
where
P.ID is not null
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Goran Djuranovic wrote:
> Hi JT,
> Thanks for your response, but you didn't answer my question.
That's because you didn't supply enough information. You didn't tell us
what "row failed" means. I assume you got an error message, so it would
help to state what the message is. Posting DDL also usually helps.
The point of JT's example is that if the error was a constraint
violation then you can use a query to filter out that row during the
INSERT. You can use a similar query to see which row(s) would have
failed. Since you didn't tell us what the error message is or show us
any data we can't give you the whole solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The short answer is, you have to use a cursor if you want isolate the exact
row where the error occured.
JT supplied you with a process for eliminating the errors, in this case dupi
cate key errors. You can also get null errors, data type conversion errors,
and constraint errors, not to mention truncation of data which can be lost
without generating an error.
Unless you know exactly what data is coming through and can identify the exp
ected errors ahead of time, you will have to use a cursor or have your appli
cation loop therough the records and make individual calls to the database,
which amounts to the same thing (although doing it in the app is more portab
le). There is another alternative, and that is to use a DTS package to load
the data and set the max errors accordingly. When the package is done inse
rting into the table you can use a select similar to what JT provided to ide
ntify the failed rows.
"Goran Djuranovic" <djurag@.mmcREMOVE_TO_MAIL.org> wrote in message news:%23V
y2uC8QGHA.2436@.TK2MSFTNGP11.phx.gbl...
Hi all,
Does anyone know if it's possible to pin-point what row failed, during the s
et operation in SQL Server 2000?
For example, I would like to insert 10 records with unique IDs ranging 1-10.
Now, during the set operation, the insert fails on record whose ID is 6. I
would like to isolate this record (get its information) and continue with th
e insert.
Is this possible or am I going to have to use LOOPs (or CURSORs)?
Also, I have to know which record failed.
Thanks in advance
Goran Djuranovic|||Have you looked at Savepoints. They are a mechanism to roll back
portions of transactions. You create a savepoint using the SAVE
TRANSACTION savepoint_name statement, and then later execute a ROLLBACK
TRANSACTION savepoint_name statement to roll back to the savepoint
instead of rolling back to the start of the whole transaction.|||Thanks to your responses, guys. I decided to go with the SET operation, and
before doing SET insert I just validate the data (not 100% safe but what is?
:-) ).
Goran Djuranovic
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142043315.953389.314120@.v46g2000cwv.googlegroups.com...
> Have you looked at Savepoints. They are a mechanism to roll back
> portions of transactions. You create a savepoint using the SAVE
> TRANSACTION savepoint_name statement, and then later execute a ROLLBACK
> TRANSACTION savepoint_name statement to roll back to the savepoint
> instead of rolling back to the start of the whole transaction.
>