Prev: 1 query; 2 different actual execution plan
Next: SQL query puzzle
From: Luigi on 4 Dec 2009 06:31
Hi all,
I have 2 table:

Create TableSource (ClientCode varchar, Field1 varchar, Field2 varchar)

Create TableTarget(ClientCode varchar, Field1 varchar, Field2 varchar)

I need to import in TableTarget every field in TableSource, for the same
ClientCode, that has different *both* Field1 and Field2.
If the ClientCode does not exists in TableTarget, obviously I import every
record.
Or, I import all the same if one of the field are different (for the same
ClientCode).
If there is matching both Field1 and Field2, for the same ClientCode, I do
not import.

Has anyone idea how to accomplish this?
I'm using SQL Server 2005.

Very thanks in advance.

Luigi

From: Plamen Ratchev on 4 Dec 2009 08:16
Here is one solution (it would have been easier to use EXCEPT but your requirement to have both columns different makes
it more difficult):

INSERT INTO [Target]
SELECT ClientCode, Field1, Field2
FROM [Source] AS S
WHERE EXISTS(SELECT *
FROM [Target] AS T
WHERE T.ClientCode = S.ClientCode
AND T.Field1 <> S.Field1
AND T.Field2 <> S.Field2)
OR NOT EXISTS(SELECT *
FROM [Target] AS T
WHERE T.ClientCode = S.ClientCode);

Here is how this will look using EXCEPT, but then again if any of the columns has different values the row will be inserted:

INSERT INTO [Target]
SELECT ClientCode, Field1, Field2
FROM [Source]
EXCEPT
SELECT ClientCode, Field1, Field2
FROM [Target];

--
Plamen Ratchev
http://www.SQLStudio.com
From: Luigi on 5 Dec 2009 11:54
"Plamen Ratchev" wrote:

> Here is one solution (it would have been easier to use EXCEPT but your requirement to have both columns different makes
> it more difficult):
>
> INSERT INTO [Target]
> SELECT ClientCode, Field1, Field2
> FROM [Source] AS S
> WHERE EXISTS(SELECT *
> FROM [Target] AS T
> WHERE T.ClientCode = S.ClientCode
> AND T.Field1 <> S.Field1
> AND T.Field2 <> S.Field2)
> OR NOT EXISTS(SELECT *
> FROM [Target] AS T
> WHERE T.ClientCode = S.ClientCode);
>
> Here is how this will look using EXCEPT, but then again if any of the columns has different values the row will be inserted:
>
> INSERT INTO [Target]
> SELECT ClientCode, Field1, Field2
> FROM [Source]
> EXCEPT
> SELECT ClientCode, Field1, Field2
> FROM [Target];

Good, thank you very much Plamen.

Luigi
From: Luigi on 9 Dec 2009 08:58
Hi Plamen,
how can I pass a parameter @CodiceCliente?
From: Plamen Ratchev on 9 Dec 2009 10:01
I do not understand, do you need this as parameter to filter the ClientCode column values? It that is the case, then
this should work:

INSERT INTO [Target]
SELECT ClientCode, Field1, Field2
FROM [Source] AS S
WHERE S.ClientCode = @CodiceCliente
AND EXISTS(SELECT *
FROM [Target] AS T
WHERE T.ClientCode = S.ClientCode
AND T.Field1 <> S.Field1
AND T.Field2 <> S.Field2)
OR NOT EXISTS(SELECT *
FROM [Target] AS T
WHERE T.ClientCode = S.ClientCode);

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: 1 query; 2 different actual execution plan
Next: SQL query puzzle