Prev: Insert Data Generate Date into an SSRS Report Header
Next: Retrieve unique values based on multiple columns
From: jonjack via SQLMonster.com on 22 Jun 2010 06:57
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

From: Plamen Ratchev on 22 Jun 2010 07:45
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
From: jonjack via SQLMonster.com on 22 Jun 2010 11:23
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

From: --CELKO-- on 23 Jun 2010 11:11
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.
From: Tony Rogerson on 23 Jun 2010 17:06
> 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(a)earthlink.net> wrote in message
news:416c70c2-a83b-4b78-9f24-3cdcf51284b1(a)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.

 |  Next  |  Last
Pages: 1 2
Prev: Insert Data Generate Date into an SSRS Report Header
Next: Retrieve unique values based on multiple columns