copy data from one table to another URGENT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

copy data from one table to another URGENT

Author

Topic

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2010-06-01 : 07:48:50

i have 2 tables "FA" and "ATE". Both have a field "DateIn".
Accidentally, i changed all the records of "DateIn" Column of table "FA" to same value. But, i have those records in table "ATE" under field "DateIn".
How can i copy data from "Datein of table ATE" TO "Datein of table FA" on match of certain condition.
very very uRgent plz help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-01 : 08:07:41

So you have to update only the column DateIn of table FA with the value of column DateIn from table ATE?
Is there another column (key) to match records in both tables?

No, you're never too old to Yak'n'Roll if you're too young to die.

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2010-06-01 : 08:16:28

quote:
Originally posted by webfred

So you have to update only the column DateIn of table FA with the value of column DateIn from table ATE?
Is there another column (key) to match records in both tables?

No, you're never too old to Yak'n'Roll if you're too young to die.

i want to update DateIn of table FA with the value of Column Datein from table ATE, WHERE the field "SerialNumber" of ATE matches with "CMSerialNumber" of table FA.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-01 : 08:23:25

No problem.
update t1
set t1.DateIn = t2.DateIn
from FA as t1
join ATE as t2
on t2.SerialNumber = t1.CMSerialNumber

No, you're never too old to Yak'n'Roll if you're too young to die.

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2010-06-01 : 09:19:46

quote:
Originally posted by webfred

No problem.
update t1
set t1.DateIn = t2.DateIn
from FA as t1
join ATE as t2
on t2.SerialNumber = t1.CMSerialNumber

No, you're never too old to Yak'n'Roll if you're too young to die.

Thank You so much....
u have saved me from a lot of trouble.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-01 : 09:25:24

welcome

Next time if you run any data changes in query window then use transaction

begin tran
update ...

and then if affected rows seems to be ok or you have made a select to see what you have done and it is ok then

commit

else if it seems not to be ok then

rollback

and all your changes are rolled back.

But think about: as long as you have not send a commit or rollback you are holding locks!

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-06-01 : 09:38:41

Agree with using a TRANSACTION block ...

... but if you have Backup and Full recovery model what's to worry about?

Or take a precautionary copy of the table before anything that may potentially need reversing / revising:


SELECT *
INTO TEMP_MyTableName_yyyymmdd
FROM MyTable

and then DROP TEMP_xxx tables after sufficient time to be notified of any problems.

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2010-06-02 : 00:54:44

quote:
Originally posted by webfred

welcome
Next time if you run any data changes in query window then use transaction

begin tran
update ...

and then if affected rows seems to be ok or you have made a select to see what you have done and it is ok then

commit

else if it seems not to be ok then

rollback

and all your changes are rolled back.

But think about: as long as you have not send a commit or rollback you are holding locks!

No, you're never too old to Yak'n'Roll if you're too young to die.

will you please explain with an example how to do this ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-02 : 01:32:05

quote:
Originally posted by rohit04413

quote:
Originally posted by webfred

welcome
Next time if you run any data changes in query window then use transaction

begin tran
update ...

and then if affected rows seems to be ok or you have made a select to see what you have done and it is ok then

commit

else if it seems not to be ok then

rollback

and all your changes are rolled back.

But think about: as long as you have not send a commit or rollback you are holding locks!

No, you're never too old to Yak'n'Roll if you're too young to die.

will you please explain with an example how to do this ?

Sorry but that was already my commented example.
begin tran
<here comes your statement>
rollback -- if it was not ok
commit -- if it was ok

No, you're never too old to Yak'n'Roll if you're too young to die.

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2010-06-02 : 01:55:52

quote:
Originally posted by webfred

quote:
Originally posted by rohit04413

quote:
Originally posted by webfred

welcome
Next time if you run any data changes in query window then use transaction

begin tran
update ...

and then if affected rows seems to be ok or you have made a select to see what you have done and it is ok then

commit

else if it seems not to be ok then

rollback

and all your changes are rolled back.

But think about: as long as you have not send a commit or rollback you are holding locks!

No, you're never too old to Yak'n'Roll if you're too young to die.

will you please explain with an example how to do this ?

Sorry but that was already my commented example.
begin tran
<here comes your statement>
rollback -- if it was not ok
commit -- if it was ok

No, you're never too old to Yak'n'Roll if you're too young to die.

does it mean that after opening a new query, i type
begin tran
<my statement>

now i execute it.
if the results are ok then i type
commit and execute it separately.

if the result not ok then i tpye
rollback and execute it.

But, if commit command updates the table then why do i need rollback? after i see that results are not ok, i don't commit.

stepson
Aged Yak Warrior

545 Posts

Posted - 2010-06-02 : 02:03:20

before an update , you can do something like this

begin tran
update ... /* this make the update */
select ... /* for the rows that where updated accordingly your condition*/
rollback tran

next step is to inspect the recordset returned by select stmt and if is ok then change the rollback tran in commit tran

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-02 : 02:07:00

But, if commit command updates the table then why do i need rollback? after i see that results are not ok, i don't commit

If you are using BEGIN TRAN then you have to do a COMMIT or a ROLLBACK otherwise the tables are locked and the results are not permanent.

No, you're never too old to Yak'n'Roll if you're too young to die.

rohit04413
Yak Posting Veteran

72 Posts

Posted - 2010-06-02 : 02:16:27

quote:
Originally posted by stepson

before an update , you can do something like this

begin tran
update ... /* this make the update */
select ... /* for the rows that where updated accordingly your condition*/
rollback tran

next step is to inspect the recordset returned by select stmt and if is ok then change the rollback tran in commit tran

thats great
thank u

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources