|
|
You're in the
sql server new users
group:is there an on key error continue???
sql server new users:
There is a key rule to prevent duplicate records. The operator might select one record, then another, then decide he really wanted all of them to be copied. Is there a way to write my stored procedure so that the duplicate key error is ignored (in the ....select-insert...) and at least all the valid records are copied? Maybe it is supposed to be doing this already and I got something else wrong??? -- ======================================================================= Beemer Biker joestateson@grandecom.net http://TipsForTheComputingImpaired.com http://ResearchRiders.org Ask about my 99'R1100RT =======================================================================
Its pretty tough to say without seeing exactly what you are doing in the sp.
But you can certainly prequalify any rows with an EXISTS statement and just not insert the ones that will be duplicates. Again without seeing what you have this is a guess but you can try something like this: INSERT INTO Table2 (Col1...) SELECT Col1... FROM Table1 WHERE NOT Exists (SELECT * FROM Table1.Key = Table2.Key) -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Beemer Biker" <jstateson@swri.edu> wrote in message news:11vn89enolu305c@corp.supernews.com... > We have a stored procedure that copies records from one day to another > day. > There is a key rule to prevent duplicate records. > > The operator might select one record, then another, then decide he really > wanted all of them to be copied. Is there a way to write my stored > procedure so that the duplicate key error is ignored (in the > ...select-insert...) and at least all the valid records are copied? > > Maybe it is supposed to be doing this already and I got something else > wrong??? > > > -- > ======================================================================= > Beemer Biker joestateson@grandecom.net > http://TipsForTheComputingImpaired.com > http://ResearchRiders.org Ask about my 99'R1100RT > ======================================================================= > >
[quoted text, click to view]
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:uGiIXX0NGHA.3164@TK2MSFTNGP11.phx.gbl... > Its pretty tough to say without seeing exactly what you are doing in the sp. > But you can certainly prequalify any rows with an EXISTS statement and just > not insert the ones that will be duplicates. Again without seeing what you > have this is a guess but you can try something like this: > > INSERT INTO Table2 (Col1...) > SELECT Col1... FROM Table1 > WHERE NOT Exists (SELECT * FROM Table1.Key = Table2.Key) > > > -- > Andrew J. Kelly SQL MVP > > > "Beemer Biker" <jstateson@swri.edu> wrote in message > news:11vn89enolu305c@corp.supernews.com... > > We have a stored procedure that copies records from one day to another > > day. > > There is a key rule to prevent duplicate records. > > > > The operator might select one record, then another, then decide he really > > wanted all of them to be copied. Is there a way to write my stored > > procedure so that the duplicate key error is ignored (in the > > ...select-insert...) and at least all the valid records are copied? > > > > Maybe it is supposed to be doing this already and I got something else > > wrong??? Thanks Andrew, but after looking at the problem I realized it was not a duplicate key error, but a rules violation. There are about 10 fields in the record that have to be unique (all of them) and that was where the problem was. The key was a guid which is unique, but we made a change to the rules. I assume that same example you gave should work, but I would have to add the 10 fields in the test for equality??? -- ======================================================================= Beemer Biker joestateson@grandecom.net http://TipsForTheComputingImpaired.com http://ResearchRiders.org Ask about my 99'R1100RT =======================================================================
Pretty much Yes. That is a lot of columns to all be unique.
-- Andrew J. Kelly SQL MVP [quoted text, click to view] "Beemer Biker" <jstateson@swri.edu> wrote in message news:11vrh8s2bfrf901@corp.supernews.com... > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:uGiIXX0NGHA.3164@TK2MSFTNGP11.phx.gbl... >> Its pretty tough to say without seeing exactly what you are doing in the > sp. >> But you can certainly prequalify any rows with an EXISTS statement and > just >> not insert the ones that will be duplicates. Again without seeing what > you >> have this is a guess but you can try something like this: >> >> INSERT INTO Table2 (Col1...) >> SELECT Col1... FROM Table1 >> WHERE NOT Exists (SELECT * FROM Table1.Key = Table2.Key) >> >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Beemer Biker" <jstateson@swri.edu> wrote in message >> news:11vn89enolu305c@corp.supernews.com... >> > We have a stored procedure that copies records from one day to another >> > day. >> > There is a key rule to prevent duplicate records. >> > >> > The operator might select one record, then another, then decide he > really >> > wanted all of them to be copied. Is there a way to write my stored >> > procedure so that the duplicate key error is ignored (in the >> > ...select-insert...) and at least all the valid records are copied? >> > >> > Maybe it is supposed to be doing this already and I got something else >> > wrong??? > > Thanks Andrew, but after looking at the problem I realized it was not a > duplicate key error, but a rules violation. There are about 10 fields in > the record that have to be unique (all of them) and that was where the > problem was. The key was a guid which is unique, but we made a change to > the rules. I assume that same example you gave should work, but I would > have to add the 10 fields in the test for equality??? > > > -- > ======================================================================= > Beemer Biker joestateson@grandecom.net > http://TipsForTheComputingImpaired.com > http://ResearchRiders.org Ask about my 99'R1100RT > ======================================================================= > >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |