Jump to next record insertion.

Hi Fellows,
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
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
--