"Andrea" <andrea@totalshareware.com> wrote in message
news:uB3ZiY18DHA.3112@tk2msftngp13.phx.gbl...
> 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
>
>

"Andrea" <andrea@totalshareware.com> wrote in message
news:OVIXyh68DHA.2924@tk2msftngp13.phx.gbl...
> 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...
> > 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" <andrea@totalshareware.com> wrote in message
> > news:uB3ZiY18DHA.3112@tk2msftngp13.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>

> 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" <andrea@totalshareware.com> wrote in message
> news:uB3ZiY18DHA.3112@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
>