joining results of query to another table

[fegdvbna22]

I have the results of a query (see below) and I want join (left join) the results of this query to another table so that I can I can get all the records that are in the query but not in the join, and then insert the result set into another table.

SELECT UploadDataStaging.CustomerName, UploadDataStaging.Street1, UploadDataStaging.Street2,
UploadDataStaging.City, UploadDataStaging.AccountNumber
FROM (SELECT MAX(AutoID) AS maxid, AccountNumber
FROM UploadDataStaging
GROUP BY AccountNumber) c INNER JOIN
UploadDataStaging ON c.maxid = UploadDataStaging.AutoID

Here is my first attempt, I'm having problems with the syntax (I didn't do the original query, it was done by a colleague). I have commented out the additional lines because I'm not sure of the correct syntax.

--insert into CompanySite([name], Address1, Address2, Town, CustomerAccountNumber
SELECT UploadDataStaging.CustomerName, UploadDataStaging.Street1, UploadDataStaging.Street2,
UploadDataStaging.City, UploadDataStaging.AccountNumber
FROM (SELECT MAX(AutoID) AS maxid, AccountNumber
FROM UploadDataStaging
GROUP BY AccountNumber) c INNER JOIN
UploadDataStaging ON c.maxid = UploadDataStaging.AutoID
--left join on CustomerSite on UploadDataStaging.AccountNumber = CustomerSite.CustomerAccountNumber
--where CustomerSite.CustomerAccountNumber is null

 

[SQLDenis]

what about this?

--insert into CompanySite([name], Address1, Address2, Town, CustomerAccountNumber )
SELECT     u.CustomerName, u.Street1, u.Street2,  u.City, u.AccountNumber 
FROM         (SELECT     MAX(AutoID) AS maxid, AccountNumber 
                       FROM          UploadDataStaging 
                       GROUP BY AccountNumber) c INNER JOIN 
                      UploadDataStaging  u ON c.maxid = u.AutoID 
                       left join CustomerSite cs on u.AccountNumber = cs.CustomerAccountNumber 
                        where cs.CustomerAccountNumber is null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[fegdvbna22]

Much to my surprise, it seems to work just by adding the insert into line at the top and the join at the bottom, without any brackets, syntax changes etc...