Hello,
I am trying to do a simple insert of records that are "new" to the source
table with the following query, but I'm not getting the expected results.
There should only be 5 new records inserted into the table yet I get 59
INSERT INTO dbo.WorkOrderMailCellXRef
SELECT DISTINCT a.emMailType,woMailTypeTemplate = Null,a.emSapieoTemplateID
FROM WorkOrderEmailsSentARchive a, WorkOrderMailCellXRef b
WHERE
( a.emMailType <> b.woMailTypeArchive
and a.emSapieoTemplateID <> b.wotemplateid)
Thank you in advance!"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:DBC88ECA-0F28-40EB-9DF0-72A12C5AD87A@.microsoft.com...
> Hello,
> I am trying to do a simple insert of records that are "new" to the source
> table with the following query, but I'm not getting the expected results.
> There should only be 5 new records inserted into the table yet I get 59
>
> INSERT INTO dbo.WorkOrderMailCellXRef
> SELECT DISTINCT a.emMailType,woMailTypeTemplate =
> Null,a.emSapieoTemplateID
> FROM WorkOrderEmailsSentARchive a, WorkOrderMailCellXRef b
> WHERE
> ( a.emMailType <> b.woMailTypeArchive
> and a.emSapieoTemplateID <> b.wotemplateid)
Not sure exactly what you're doing, but maybe an example of using <> as the
join condition in the WHERE clause will help. We'll populate 2 tables with
4 rows of two colors each, then run an INNER JOIN using <> like you did
above. Even though there are only 8 total rows in both tables, the results
is 11 rows because of the <> :
CREATE TABLE #a (color1 VARCHAR(16),
color2 VARCHAR(16))
CREATE TABLE #b (color1 VARCHAR(16),
color2 VARCHAR(16))
INSERT INTO #a (color1, color2)
SELECT 'blue', 'red'
UNION SELECT 'blue', 'green'
UNION SELECT 'black', 'yellow'
UNION SELECT 'blue', 'blue'
INSERT INTO #b (color1, color2)
SELECT 'blue', 'green'
UNION SELECT 'red', 'white'
UNION SELECT 'yellow', 'purple'
UNION SELECT 'black', 'blue'
SELECT DISTINCT a.color1, a.color2, b.color1, b.color2
FROM #a a, #b b
WHERE (a.color1 <> b.color1
AND a.color2 <> b.color2)
DROP TABLE #a
DROP TABLE #b
If you can describe what you're doing with DDL, sample data, and expected
result it would be helpful.|||Maybe you meant to do something like this?
INSERT INTO dbo.WorkOrderMailCellXRef
SELECT DISTINCT a.emMailType,woMailTypeTemplate = Null,a.emSapieoTemplateID
FROM WorkOrderEmailsSentARchive a
WHERE not exists
(
select 1 from WorkOrderMailCellXRef b
where a.emMailType = b.woMailTypeArchive
and a.emSapieoTemplateID = b.wotemplateid
)
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:DBC88ECA-0F28-40EB-9DF0-72A12C5AD87A@.microsoft.com...
> Hello,
> I am trying to do a simple insert of records that are "new" to the source
> table with the following query, but I'm not getting the expected results.
> There should only be 5 new records inserted into the table yet I get 59
>
> INSERT INTO dbo.WorkOrderMailCellXRef
> SELECT DISTINCT a.emMailType,woMailTypeTemplate =
Null,a.emSapieoTemplateID
> FROM WorkOrderEmailsSentARchive a, WorkOrderMailCellXRef b
> WHERE
> ( a.emMailType <> b.woMailTypeArchive
> and a.emSapieoTemplateID <> b.wotemplateid)
>
> Thank you in advance!
>
No comments:
Post a Comment