|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
temporarily allow duplicate keys?Hi,
We have a case where there will be temporarly duplicate keys during the update process. For example, Key "A" becomes "B" and "B" becomes "C". When "A" is set to "B", we get a duplicate key error because "B" already exists in the table. Is there a way to temporarly disable all constraints at the beginning of a batch update and enable it again when we're all done? I've tried ALTER TABLE name NOCHECK CONSTRAINT ALL, but this only seems to affect foreign keys. Steven - - - Steven
If you have a PRIMARY KEY on the column , drop the constraint first and the re-create -it. It does not make sence to play with the data on the column that does not allow duplicates Show quote "Steven Spits" <nospam@company.com> wrote in message news:uBpOEaRSHHA.4260@TK2MSFTNGP06.phx.gbl... > Hi, > > We have a case where there will be temporarly duplicate keys during the > update process. For example, Key "A" becomes "B" and "B" becomes "C". When > "A" is set to "B", we get a duplicate key error because "B" already exists > in the table. > > Is there a way to temporarly disable all constraints at the beginning of a > batch update and enable it again when we're all done? > > I've tried ALTER TABLE name NOCHECK CONSTRAINT ALL, but this only seems to > affect foreign keys. > > Steven > > - - - > > strange that you would want to do this, but there are a number of ways.
Drop and recreate the key or play with the data in a temporary table first. Show quote "Steven Spits" <nospam@company.com> wrote in message news:uBpOEaRSHHA.4260@TK2MSFTNGP06.phx.gbl... > Hi, > > We have a case where there will be temporarly duplicate keys during the > update process. For example, Key "A" becomes "B" and "B" becomes "C". When > "A" is set to "B", we get a duplicate key error because "B" already exists > in the table. > > Is there a way to temporarly disable all constraints at the beginning of a > batch update and enable it again when we're all done? > > I've tried ALTER TABLE name NOCHECK CONSTRAINT ALL, but this only seems to > affect foreign keys. > > Steven > > - - - > > Steven Spits (nospam@company.com) writes:
> We have a case where there will be temporarly duplicate keys during the That's right, you cannot disable a primary key. Or more generally: you > update process. For example, Key "A" becomes "B" and "B" becomes "C". When > "A" is set to "B", we get a duplicate key error because "B" already exists > in the table. > > Is there a way to temporarly disable all constraints at the beginning of a > batch update and enable it again when we're all done? > > I've tried ALTER TABLE name NOCHECK CONSTRAINT ALL, but this only seems to > affect foreign keys. cannot disable a unique index to be non-unique. (Actually, you can disable an index in SQL 2005, but at the end you need to drop it and recreate it.) The best strategy in my opinion would be to perform the process so that duplicates does not occur. That is, first set B to C and then A to B. First setting A to B sounds scary to me, since how do you know which B to change to C? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hey,
> The best strategy in my opinion would be to perform the process so that You're absolutely right, must be my brain that's still in weekend-mode.> duplicates does not occur. That is, first set B to C and then A to B. > First setting A to B sounds scary to me, since how do you know which B > to change to C? Thanks all! Steven - - - i have a similar problem (i've already posted a message but just now
i' seen this one). I want to make an insert from a DataTable to a SQL Database, i wan't to ignore the error that brings when you try to insert a duplicate key. I don't want to insert it anyway (ignore the primary key) i just want to ignore the error mesagge, don't insert that value and continue to keep inserting the other values. Now i do a bulk copy to a temporary table and then insert de values that are not duplicate with a SP. But i want to insert the data with a single bulkcopy or insert command directly from .net Thanks Show quote On 5 feb, 09:44, "Steven Spits" <nos***@company.com> wrote: > Hey, > > > The best strategy in my opinion would be to perform the process so that > > duplicates does not occur. That is, first set B to C and then A to B. > > First setting A to B sounds scary to me, since how do you know which B > > to change to C? > > You're absolutely right, must be my brain that's still in weekend-mode. > > Thanks all! > > Steven > > - - - Arnoled,
Set the property for that from the dataadapter. It is standard what you ask. Cannot get the name of that property so I would have to search as you now on msdn for that. Cor Show quote "arnoled" <abdala.ferna***@gmail.com> schreef in bericht news:1170693233.063832.296040@h3g2000cwc.googlegroups.com... >i have a similar problem (i've already posted a message but just now > i' seen this one). > > I want to make an insert from a DataTable to a SQL Database, i wan't > to ignore the error that brings when you try to insert a duplicate > key. I don't want to insert it anyway (ignore the primary key) i just > want to ignore the error mesagge, don't insert that value and continue > to keep inserting the other values. > > Now i do a bulk copy to a temporary table and then insert de values > that are not duplicate with a SP. But i want to insert the data with a > single bulkcopy or insert command directly from .net > > Thanks > > On 5 feb, 09:44, "Steven Spits" <nos***@company.com> wrote: >> Hey, >> >> > The best strategy in my opinion would be to perform the process so that >> > duplicates does not occur. That is, first set B to C and then A to B. >> > First setting A to B sounds scary to me, since how do you know which B >> > to change to C? >> >> You're absolutely right, must be my brain that's still in weekend-mode. >> >> Thanks all! >> >> Steven >> >> - - - > > Sorry for the late response.
Yes, i did use a dataadater to make the insert. I set up an InsertCommand and set de rows of the DataTable to Update or something like that so they'd be inserted in the table. I made a SP that inserted the items that weren't in the table (no duplicates). Thanks Show quote On 8 feb, 03:09, "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote: > Arnoled, > > Set the property for that from the dataadapter. It is standard what you ask. > > Cannot get the name of that property so I would have to search as you now on > msdn for that. > > Cor > > "arnoled" <abdala.ferna***@gmail.com> schreef in berichtnews:1170693233.063832.296***@h3g2000cwc.googlegroups.com... > > >i have a similar problem (i've already posted a message but just now > > i' seen this one). > > > I want to make an insert from a DataTable to a SQL Database, i wan't > > to ignore the error that brings when you try to insert a duplicate > > key. I don't want to insert it anyway (ignore the primary key) i just > > want to ignore the error mesagge, don't insert that value and continue > > to keep inserting the other values. > > > Now i do a bulk copy to a temporary table and then insert de values > > that are not duplicate with a SP. But i want to insert the data with a > > single bulkcopy or insert command directly from .net > > > Thanks > > > On 5 feb, 09:44, "Steven Spits" <nos***@company.com> wrote: > >> Hey, > > >> > The best strategy in my opinion would be to perform the process so that > >> > duplicates does not occur. That is, first set B to C and then A to B. > >> > First setting A to B sounds scary to me, since how do you know which B > >> > to change to C? > > >> You're absolutely right, must be my brain that's still in weekend-mode. > > >> Thanks all! > > >> Steven > > >> - - -  |
|||||||||||||||||||||||
Other interesting topics