SQL Server - update table question

Asked By EddWood

18-Nov-09 01:03 PM

MSSQL 2005

I have this script that updates items in a table A if the codeID are equal
in table B. However, I also need to add any records to table A if there is a
record in table B that does not currently exists in table A, can anyone
advise how I can modify this or add a new script that will update/insert any
new records

thanks
Ed

script>>

UPDATE d
SET d.[T1_Code] = p.[Code],
d.[T1_productname]= p.[Product Name],
d.[T1_rrp] = p.[Unit RRP],
d.[T1_VAT] = p.[VAT Rate],
d.[T1_barcode] = p.[Barcode],
FROM [DS_T1] d
RIGHT JOIN [T2] p
ON d.[T1_code]= p.[Code];

18-Nov-09 06:51 PM

EddWood (Edd_nospam_@/Wood\hotmail.co.uk) writes:

RIGHT JOIN should probably only be JOIN. I am not even sure that I under-
stand what the semantics for RIGHT JOIN would be here.

The INSERT statement you need would be:

INSERT DS_T1(T1_Code, T1_productname, T1_rrp, T1_VAT, T1_barcode)
SELECT p.Code, p.[Product Name], p.[Unit RRP], p.[VAT Rate], p.Barcode
FROM   T2 p
WHERE  NOT EXISTS (SELECT *
FROM   DS_T1 d
WHERE  d.T1_Code = p.Code)


I am only using brackets where they are required for better legibility.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

19-Nov-09 04:32 AM

Perfect, thank you