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 '11 at 08:12 AM in Default

zadien gravatar image

zadien
5 3 3 3

(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 '11 at 08:19 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Great that worked well. Thanks
Oct 07 '11 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 '11 at 08:44 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

(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 '11 at 01:59 PM

JamieC gravatar image

JamieC
126 1 2

Thanks for this solution.
Oct 07 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x128

asked: Oct 06 '11 at 08:12 AM

Seen: 985 times

Last Updated: Oct 06 '11 at 08:16 AM