x

Merge data from one table to another using UPDATE

Hi,

I need to insert a few thousand rows of data from one table to another, the destination table already has the primary key column filled out so I only need the specified columns populated...

I have go to the point with the below statement that it is updating but taking the first record for all inserts.

So far I have:

 update tableDERP
 set  BarcodeRef = a.Barcode
     ,CustName = a.CustomerName
     ,CustCode = a.CustomerCode
     ,DocDate = a.[Date]
     ,Name = a.Name
     ,VehicleReg = a.VehicleRegistration
     ,Blanket = a.Blanket
     ,CustPONo =  a.CustomerPO
     ,SOType = a.[Type]
     from
     (select a.Barcode, a.CustomerName, a.CustomerCode,a.[Date], 
       a.Name, a.VehicleRegistration, a.Blanket, a.CustomerPO, a.[Type]
     from BOOKINGID a, tableDERP s
     where a.BookingID = s.BarcodeRef
 ) as a

Am I on the right track?

A fresh pair of eyes would be most welcome.

more ▼

asked Oct 06, 2011 at 08:12 AM in Default

avatar image

zadien
5 3 3 4

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Why the subquery? And use the preferred join syntax of table JOIN table ON

 update  s
 set     BarcodeRef = a.Barcode ,
         CustName = a.CustomerName ,
         CustCode = a.CustomerCode ,
         DocDate = a.[Date] ,
         Name = a.Name ,
         VehicleReg = a.VehicleRegistration ,
         Blanket = a.Blanket ,
         CustPONo = a.CustomerPO ,
         SOType = a.[Type]
 from    tableDERP s
 join    BOOKINGID a  on a.BookingID = s.BarcodeRef
more ▼

answered Oct 06, 2011 at 08:19 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

Great that worked well. Thanks

Oct 07, 2011 at 01:24 AM zadien
(comments are locked)
10|1200 characters needed characters left

An alternative to @kev riley's approach would be to do a MERGE statement, particularly if you suspect there's data missing. This only applies to SQL Server 2008 or later. See http://technet.microsoft.com/en-us/library/bb510625.aspx for more info.

more ▼

answered Oct 06, 2011 at 08:44 AM

avatar image

ThomasRushton ♦♦
39.7k 20 47 52

(comments are locked)
10|1200 characters needed characters left

Here's my preferred syntax i.e. using `MERGE`:

 MERGE INTO tableDERP
    USING BOOKINGID AS a
       ON a.BookingID = tableDERP.BarcodeRef
 WHEN MATCHED THEN
 UPDATE
    SET BarcodeRef = a.Barcode
       ,CustName = a.CustomerName
       ,CustCode = a.CustomerCode
       ,DocDate = a.[Date]
       ,Name = a.Name
       ,VehicleReg = a.VehicleRegistration
       ,Blanket = a.Blanket
       ,CustPONo =  a.CustomerPO
       ,SOType = a.[Type];
more ▼

answered Oct 06, 2011 at 01:59 PM

avatar image

JamieC
126 1 3

Thanks for this solution.

Oct 07, 2011 at 04:20 AM zadien
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x161

asked: Oct 06, 2011 at 08:12 AM

Seen: 1821 times

Last Updated: Oct 06, 2011 at 08:16 AM

Copyright 2016 Redgate Software. Privacy Policy