andrea

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

andrew j. kelly

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

andrea

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.

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> ha scritto nel messaggio
news:uBqjjg18DHA.712@tk2msftngp13.phx.gbl...

take

andrew j. kelly

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