INSERT not in

SQL Server Developer >> INSERT not in

I need some help with the INSERT (not IN) combo.
basically Im trying to insert the records than are in table A but not table B.
Using ContactID field to check if it is both tables, update the ones that
are there (which I allready have) and then insert the ones that are not.

Using the NOT IN statement.

Any ideas? thanks all!
--
SKOT VOID
1010011010

SQL Server29

SQL Server Developer >> INSERT not in

INSERT INTO TableB
SELECT <columns>
FROM TABLEA
WHERE NOT EXISTS ( SELECT 1 from TableB WHERE TableA.ID = TableB.ID)

hope this will help u

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---------------------------------------

> I need some help with the INSERT (not IN) combo.
> basically Im trying to insert the records than are in table A but not table B.
> Using ContactID field to check if it is both tables, update the ones that
> are there (which I allready have) and then insert the ones that are not.
>
> Using the NOT IN statement.
>
> Any ideas? thanks all!
> --
> SKOT VOID
> 1010011010
>

SQL Server Developer >> INSERT not in Merge logic... alas, T-SQL still doesn't support it with a built-in
statement.

To update the ones that are already in the table you can use:

UPDATE T
SET c1 = S.c1, c2 = S.c2, etc.
FROM Target AS T
JOIN Source AS S
ON T.key = S.key;

To load non-existing rows, as Chanda suggested, use:

INSERT INTO Target(col_list)
SELECT col_list FROM Source AS S
WHERE NOT EXISTS
(SELECT * FROM Target AS T WHERE T.key = S.key);

--
BG, SQL Server MVP
www.SolidQualityLearning.com

>I need some help with the INSERT (not IN) combo.
> basically Im trying to insert the records than are in table A but not
> table B.
> Using ContactID field to check if it is both tables, update the ones that
> are there (which I allready have) and then insert the ones that are not.
>
> Using the NOT IN statement.
>
> Any ideas? thanks all!
> --
> SKOT VOID
> 1010011010
>

SQL Server Developer >> INSERT not in

> Merge logic... alas, T-SQL still doesn't support it with a built-in
> statement.
>
> To update the ones that are already in the table you can use:
>
> UPDATE T
> SET c1 = S.c1, c2 = S.c2, etc.
> FROM Target AS T
> JOIN Source AS S
> ON T.key = S.key;
>
> To load non-existing rows, as Chanda suggested, use:
>
> INSERT INTO Target(col_list)
> SELECT col_list FROM Source AS S
> WHERE NOT EXISTS
> (SELECT * FROM Target AS T WHERE T.key = S.key);
>
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
>

>>I need some help with the INSERT (not IN) combo.
>> basically Im trying to insert the records than are in table A but not
>> table B.
>> Using ContactID field to check if it is both tables, update the ones that
>> are there (which I allready have) and then insert the ones that are not.
>>
>> Using the NOT IN statement.
>>
>> Any ideas? thanks all!
>> --
>> SKOT VOID
>> 1010011010
>>
>
>

For large tables where both the parent and child tables have a lot of rows,
I have found the following to work a little faster. I do a LEFT JOIN from
the Parent table to the Child table and insert the rows where the Child
table's join column IS NULL.

INSERT Child(x, y)
SELECT P.x, P.y
FROM Parent P
LEFT JOIN Child C ON p.x = c.x
WHERE c.x IS NULL

It's very fast when you have indexes on the FK columns.

Rick