update table question

[EddWood]

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];

[Erland Sommarskog]

EddWood (Edd_nospam_@/Wood\hotmail.co.uk) writes:
> 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
>
>

> 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];

RIGHT JOIN should probably only be JOIN. I'm 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'm only using brackets where they are required for better legibility.

--
Erland Sommarskog, SQL Server MVP, esq...@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

[EddWood]

Perfect, thank you

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CC7F063A...@127.0.0.1...