how to insert records where PK is being violated on some records?

Hello,
I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
tbl2.ID is a PK. The problem is that tbl2 already contains some of the
records from tbl1 whcih is causing a PK violation. what tsql statement can
perform the insert without violating PK?
Insert Into tbl2(ID, fld2, fld3)
Select ID, fld2, fld3 from tbl1
where tbl1.ID Not In (Select ID from tbl2)
Any suggestions appreciated.
Thanks,
RichBegin by writing a query that only selects those rows from [tbl1] that do
not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
you may then insert this result into [tbl2]. This may perform better than
the NOT IN or NOT EXISTS method.
Insert Into
tbl2 (ID, fld2, fld3)
Select
T1.ID,
T1.fld2,
T1.fld3
from tbl1
left join tbl2 as T2
on T2.ID = T1.ID
where
T2.ID is null
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement
> can
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich|||Thank you all for your replies.
"Rich" wrote:

> Hello,
> I need to insert some records from tbl1 to tbl2. tbl1.ID is a pk and
> tbl2.ID is a PK. The problem is that tbl2 already contains some of the
> records from tbl1 whcih is causing a PK violation. what tsql statement c
an
> perform the insert without violating PK?
> Insert Into tbl2(ID, fld2, fld3)
> Select ID, fld2, fld3 from tbl1
> where tbl1.ID Not In (Select ID from tbl2)
> Any suggestions appreciated.
> Thanks,
> Rich

how to insert records where PK is being violated on some recor

How about this
Insert Into tbl2(ID, fld1, fld2)
Select Id,fld1, fld2 from
tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
Left Join ttbl2 t2 on t1.id = t2.id
where t2.id Is Null
Would this work also? I forgot that I left out another table.
"JT" wrote:

> Begin by writing a query that only selects those rows from [tbl1] that do
> not exist in [tbl2]. This can be done using a LEFT JOIN on ID. Once done,
> you may then insert this result into [tbl2]. This may perform better than
> the NOT IN or NOT EXISTS method.
> Insert Into
> tbl2 (ID, fld2, fld3)
> Select
> T1.ID,
> T1.fld2,
> T1.fld3
> from tbl1
> left join tbl2 as T2
> on T2.ID = T1.ID
> where
> T2.ID is null
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:05766B06-11C8-45D1-BC2E-D1CD0C30576F@.microsoft.com...
>
>It looks like it would.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:00FBAEDE-5940-48DA-806D-5EB176AD10D7@.microsoft.com...
> How about this
> Insert Into tbl2(ID, fld1, fld2)
> Select Id,fld1, fld2 from
> tbl1 t1 join tbl3 t3 on t1.ID = t3.ID
> Left Join ttbl2 t2 on t1.id = t2.id
> where t2.id Is Null
> Would this work also? I forgot that I left out another table.
> "JT" wrote:
>