Help with complex insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with complex insert

Author

Topic

Balance
Starting Member

24 Posts

Posted - 2005-02-15 : 15:44:49

I need to add records to the contacts table if an email address isn't associated with a matching account id. For example:

I have the following code but it isn't working right...

INSERT INTO dbo.contacts fname, lname, emailAddress
SELECT DISTINCT fname, lname, emailAddress FROM ##contacts_tmp contacts_tmp INNER JOIN ##acctID_emailAddress_tmp acctID_email_tmp
ON contacts_tmp.emailAddress = acctID_email_tmp.emailAddress
INNER JOIN xref282Contacts
ON acctID_email_tmp.account_id <> xref282Contacts.element_id

TIA

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-15 : 15:48:16

try:
INSERT INTO dbo.contacts (fname, lname, emailAddress)
SELECT DISTINCT fname, lname, emailAddress
FROM ##contacts_tmp contacts_tmp
INNER JOIN ##acctID_emailAddress_tmp acctID_email_tmp
ON contacts_tmp.emailAddress = acctID_email_tmp.emailAddress
LEFT JOIN xref282Contacts
ON acctID_email_tmp.account_id = xref282Contacts.element_id
where xref282Contacts.element_id is null

Go with the flow & have fun! Else fight the flow

Balance
Starting Member

24 Posts

Posted - 2005-02-15 : 16:06:11

Tried it and no records are getting added to the contacts table. FWIW - If I remove the "where xref282Contacts.element_id is null" clause it adds the (same) record each time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources