Help Primary Key violation 
Author Message
 Help Primary Key violation

I'm trying to port access DB to Sql Server database.
With access I can insert several rows also if someone are present in the
destination table.
For example if I have 20 rows in the dest table and I want to insert 30 rows
from orig table to dest table, but 20 rows are present in the dest table, an
insert command, will insert the 10 new rows and return a message of primary
key violation.

Sql Server, return primary key violation message, but don't insert the new
10 rows.
How can I do?
The problem is that the destination table have 3.000.000 of records and
source table have 400.000 record.

Thanks for your help.

    Andrew



Thu, 03 Aug 2006 00:56:02 GMT
 Help Primary Key violation

Well I wouldn't recommend doing all the inserts in one batch as it may take
a while and probably grow your tran log quite a bit.  But you can use NOT
EXISTS to do this.

 INSERT INTO TableA (PK, Col2, Col3...)
    SELECT b.PK, b.Col2, b.Col3.. FROM TableB AS b
            WHERE NOT EXISTS (SELECT * FROM TableA AS a WHERE a.PK = b.PK)

Or you could remove the duplicates first and then just Insert.

--

Andrew J. Kelly
SQL Server MVP


Quote:
> I'm trying to port access DB to Sql Server database.
> With access I can insert several rows also if someone are present in the
> destination table.
> For example if I have 20 rows in the dest table and I want to insert 30
rows
> from orig table to dest table, but 20 rows are present in the dest table,
an
> insert command, will insert the 10 new rows and return a message of
primary
> key violation.

> Sql Server, return primary key violation message, but don't insert the new
> 10 rows.
> How can I do?
> The problem is that the destination table have 3.000.000 of records and
> source table have 400.000 record.

> Thanks for your help.

>     Andrew



Thu, 03 Aug 2006 01:10:35 GMT
 Help Primary Key violation
Thanks Andrew,
do you think is better use NOT EXISTS or use LEFT JOIN where the right row
is null?
This is the example:

INSERT INTO TableA (PK, Col2, Col3...)
    SELECT b.PK, b.Col2, b.Col3..
       FROM TableB AS b LEFT JOIN TableA ON b.PK=TableA.PK
         WHERE TableA.PK IS NULL

Which is faster?
Thanks again.



Quote:
> Well I wouldn't recommend doing all the inserts in one batch as it may
take
> a while and probably grow your tran log quite a bit.  But you can use NOT
> EXISTS to do this.

>  INSERT INTO TableA (PK, Col2, Col3...)
>     SELECT b.PK, b.Col2, b.Col3.. FROM TableB AS b
>             WHERE NOT EXISTS (SELECT * FROM TableA AS a WHERE a.PK = b.PK)

> Or you could remove the duplicates first and then just Insert.

> --

> Andrew J. Kelly
> SQL Server MVP



> > I'm trying to port access DB to Sql Server database.
> > With access I can insert several rows also if someone are present in the
> > destination table.
> > For example if I have 20 rows in the dest table and I want to insert 30
> rows
> > from orig table to dest table, but 20 rows are present in the dest
table,
> an
> > insert command, will insert the 10 new rows and return a message of
> primary
> > key violation.

> > Sql Server, return primary key violation message, but don't insert the
new
> > 10 rows.
> > How can I do?
> > The problem is that the destination table have 3.000.000 of records and
> > source table have 400.000 record.

> > Thanks for your help.

> >     Andrew



Thu, 03 Aug 2006 10:45:24 GMT
 Help Primary Key violation
EXISTS will usually be as fast or faster in almost all cases but it always
pays to test for your particular conditions.
--

Andrew J. Kelly
SQL Server MVP


Quote:
> Thanks Andrew,
> do you think is better use NOT EXISTS or use LEFT JOIN where the right row
> is null?
> This is the example:

> INSERT INTO TableA (PK, Col2, Col3...)
>     SELECT b.PK, b.Col2, b.Col3..
>        FROM TableB AS b LEFT JOIN TableA ON b.PK=TableA.PK
>          WHERE TableA.PK IS NULL

> Which is faster?
> Thanks again.



> > Well I wouldn't recommend doing all the inserts in one batch as it may
> take
> > a while and probably grow your tran log quite a bit.  But you can use
NOT
> > EXISTS to do this.

> >  INSERT INTO TableA (PK, Col2, Col3...)
> >     SELECT b.PK, b.Col2, b.Col3.. FROM TableB AS b
> >             WHERE NOT EXISTS (SELECT * FROM TableA AS a WHERE a.PK =
b.PK)

> > Or you could remove the duplicates first and then just Insert.

> > --

> > Andrew J. Kelly
> > SQL Server MVP



> > > I'm trying to port access DB to Sql Server database.
> > > With access I can insert several rows also if someone are present in
the
> > > destination table.
> > > For example if I have 20 rows in the dest table and I want to insert
30
> > rows
> > > from orig table to dest table, but 20 rows are present in the dest
> table,
> > an
> > > insert command, will insert the 10 new rows and return a message of
> > primary
> > > key violation.

> > > Sql Server, return primary key violation message, but don't insert the
> new
> > > 10 rows.
> > > How can I do?
> > > The problem is that the destination table have 3.000.000 of records
and
> > > source table have 400.000 record.

> > > Thanks for your help.

> > >     Andrew



Thu, 03 Aug 2006 15:06:23 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

2. Primary Key Violation - Please HELP !!!!!

3. reworded: help on Violation of PRIMARY KEY constraint 'PK_TBL_Mytable

4. primary key violation need help

5. Help!! Primary Key Violation

6. Violation of PRIMARY KEY constraint ??

7. Violation of primary key on backup

8. Violation of primary key constraint

9. primary key access violation?

10. Q: Primary key constraint violation with datetime field

11. ODBC Violation of Primary Key Constraint Can't Update Reord


 
Powered by phpBB® Forum Software