Hi

Im a SQL Server newbie and am a bit confused by the number of different
methods I have seen posted on the web about approaching this problem.

I am using SQL Server 2005 and I have about 500 records defined in simple
terms for brevity:


ID  |   EMAIL  |  NAME  |  etc

I have generated INSERT statements for all the records via Management Studio
| Tasks | Generate Scripts.

Im migrating this data to anotaher db that already has records and there are
some duplicates with my data. I need to do a check that the record does not
exist and then do an INSERT if not (Im not doing any UPDATEs). The check
should be on the EMAIL field.

Can anyone advise me on the 'simplest' approach?
Im not too familiar with transactions is that the way forward?



regarda
Jon

-- 
Message posted via http://www.sqlmonster.com

A very simple approach is:

INSERT INTO TargetTable (id, email, name)
SELECT id, email, name
FROM SourceTable AS S
WHERE NOT EXISTS(
   SELECT *
   FROM TargetTable AS T
   WHERE T.email = S.email);

--
Plamen Ratchev
http://www.SQLStudio.com

Plaman



Worked well.
Thanks very much for taking the time to comment and help me out.


Brilliant
regards
Jon



Plamen Ratchev wrote:
>A very simple approach is:
>
>INSERT INTO TargetTable (id, email, name)
>SELECT id, email, name
>FROM SourceTable AS S
>WHERE NOT EXISTS(
>   SELECT *
>   FROM TargetTable AS T
>   WHERE T.email = S.email);
>
>--
>Plamen Ratchev
>http://www.SQLStudio.com

-- 
Message posted via http://www.sqlmonster.com

A more set-oriented way uses set operations now that we have them.

INSERT INTO TargetTable (message_id, email_addr, recipient_name)
SELECT message_id, email_addr, recipient_name
  FROM (SELECT * FROM SourceTable
         EXCEPT
        SELECT * FROM TargetTable)
       AS S_minus_T (message_id, email_addr, recipient_name);

I have no idea what performance is like, but in Oracle and DB2 which
has had them for a long time, they are well optimized.

> INSERT INTO TargetTable (message_id, email_addr, recipient_name)
> SELECT message_id, email_addr, recipient_name
>  FROM (SELECT * FROM SourceTable
>         EXCEPT
>        SELECT * FROM TargetTable)
>       AS S_minus_T (message_id, email_addr, recipient_name);

That's a really poor example of good SQL.

You should never use * in table expressions in production code.

--ROGGIE--

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:416c70c2-a83b-4b78-9f24-3cdcf51284b1@w31g2000yqb.googlegroups.com...
> A more set-oriented way uses set operations now that we have them.
>
> INSERT INTO TargetTable (message_id, email_addr, recipient_name)
> SELECT message_id, email_addr, recipient_name
>  FROM (SELECT * FROM SourceTable
>         EXCEPT
>        SELECT * FROM TargetTable)
>       AS S_minus_T (message_id, email_addr, recipient_name);
>
> I have no idea what performance is like, but in Oracle and DB2 which
> has had them for a long time, they are well optimized. 

This will not work because the key is the email column. Using EXCEPT
can potentially insert rows with duplicate emails if any other column
differs. MERGE would be a better option on SQL Server 2008, but here
the requirement is SQL Server 2005.

--
Plamen Ratchev
http://www.SQLStudio.com

>> This will not work because the key is the email column. <<

We don't have any DDL, so I don't know what the key is. I have the
horrible feeling it is the vague, generic "id" which might be an
IDENTITY column (ugh)!

>> Using EXCEPT can potentially insert rows with duplicate emails if any other column differs. <<

But then they are not duplicates :)

>> MERGE would be a better option on SQL Server 2008, but here the requirement is SQL Server 2005. <<

Agreed.

The poster stated EMAIL hence Plamen comment no doubt.

--ROGGIE--

"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:a770c009-4e9b-42dd-99ea-ef1ee8b7a0fb@i31g2000yqm.googlegroups.com...
>>> This will not work because the key is the email column. <<
>
> We don't have any DDL, so I don't know what the key is. I have the
> horrible feeling it is the vague, generic "id" which might be an
> IDENTITY column (ugh)!
>
>>> Using EXCEPT can potentially insert rows with duplicate emails if any 
>>> other column differs. <<
>
> But then they are not duplicates :)
>
>>> MERGE would be a better option on SQL Server 2008, but here the 
>>> requirement is SQL Server 2005. <<
>
> Agreed. 

Deberías investigar el UPSERT o MERGE, en el cual se utiliza una tabla 
temporal o derived table en la cual insertas
los registros y efectúas un JOIN, lo que cruza es UPDATE, lo que es nuevo 
INSERT.

Esto te ayuda?

Saludos


"jonjack via SQLMonster.com" <u61099@uwe> escribió en el mensaje de 
noticias:a9e73e845f9a6@uwe...
> Hi
>
> Im a SQL Server newbie and am a bit confused by the number of different
> methods I have seen posted on the web about approaching this problem.
>
> I am using SQL Server 2005 and I have about 500 records defined in simple
> terms for brevity:
>
>
> ID  |   EMAIL  |  NAME  |  etc
>
> I have generated INSERT statements for all the records via Management 
> Studio
> | Tasks | Generate Scripts.
>
> Im migrating this data to anotaher db that already has records and there 
> are
> some duplicates with my data. I need to do a check that the record does 
> not
> exist and then do an INSERT if not (Im not doing any UPDATEs). The check
> should be on the EMAIL field.
>
> Can anyone advise me on the 'simplest' approach?
> Im not too familiar with transactions is that the way forward?
>
>
>
> regarda
> Jon
>
> -- 
> Message posted via http://www.sqlmonster.com
>