copy data from one table to another table same structure

Question

copy data from one table to another table same structure

montserrat.deza

Right there with Babe

Points: 725
More actions
May 25, 2018 at 6:02 pm

#358472

Hello could you help me to write this query? I need to update from package.serialnumber the values of package_backup serialnumber.
They are same tables with same structure but not sure why the following query is not working. Could you help?
Thank you!
UPDATE
    package
SET
    serialnumber = PB.Serialnumber
FROM
    Package P,package_backup PB
where P.id=PB.id and P.PackageStatusId=1;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 1

montserrat.deza - Friday, May 25, 2018 6:02 PM
Hello could you help me to write this query? I need to update from package.serialnumber the values of package_backup serialnumber.
They are same tables with same structure but not sure why the following query is not working. Could you help?
Thank you!
UPDATE
    package
SET
    serialnumber = PB.Serialnumber
FROM
    Package P,package_backup PB
where P.id=PB.id and P.PackageStatusId=1;

Try this
😎
UPDATE P SET P.serialnumber = PB.Serialnumber FROM Package P INNER JOIN package_backup PB ON P.id = PB.id AND P.PackageStatusId = 1;

One reason your query could be failing is the cross join implied in the from clause, without any further information, there is no way to tell.

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 2

Eirikur Eiriksson - Saturday, May 26, 2018 1:35 AM
montserrat.deza - Friday, May 25, 2018 6:02 PM
Hello could you help me to write this query? I need to update from package.serialnumber the values of package_backup serialnumber.
They are same tables with same structure but not sure why the following query is not working. Could you help?
Thank you!
UPDATE
    package
SET
    serialnumber = PB.Serialnumber
FROM
    Package P,package_backup PB
where P.id=PB.id and P.PackageStatusId=1;
Try this
😎
UPDATE P SET P.serialnumber = PB.Serialnumber FROM   Package      P INNER JOIN package_backup    PB ON    P.id      = PB.id AND   P.PackageStatusId = 1;
One reason your query could be failing is the cross join implied in the from clause, without any further information, there is no way to tell.

To add to that, you MUST include the target of the update in the FROM clause for joined updates like Eirikur did or you could end up with an update that will usually seem to work just fine and then someday... WHAM! A 6 second update will suddenly drive several CPUs to the stops and take hours to execute or fail due to a "halloweening-like" actual execution plan that won't show up as being so in an estimated execution plan even if the update doesn't cause the data movement that typical of the true "Halloweening" problem in databases.

Also, not having the target of the update in the FROM clause for joined updates is actually an illegal and certainly not supported form of UPDATE. You won't find any joined UPDATE example that doesn't have the target of the update in the FROM clause anywhere in Books Online or other Microsoft generated documentation.,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)