| Author |
Topic |
|
wrz234
Starting Member
8 Posts |
 Posted - 2010-04-07 : 03:56:34
|
| Dear all,I have two table, 'claim' and 'claim archive'. i wrote this statement to move data from table 'claim' to 'claim archive'. Assume these two table have the same column. SQL statement:insert into claim archiveselect * from claimBut how to delete the data at claim after move? Is it using delete * from claim?insert into claim archiveselect * from claimdelete * from claimThanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-07 : 04:00:44
|
Which version of SQL Server are you using?Is it correct to insert and delete data WITHOUT any where clause?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-07 : 04:03:17
|
Looks about right. There is no "*" required in DELETE FROM CLAIM.This will copy EVERYTHING from Claim into Archive, and then delete EVERYTHING in Claim - is that what you want?There is also a risk that someone might add a record to CLAIM between you Copying to Archive and then Deleting - i.e. you will delete their Claim before it has been copied to Archive  What version of SQL Server are you using? (SQL2008 SQL2005 has a neat way of solving this problem)Edit:  |
|
|
|
wrz234
Starting Member
8 Posts |
Posted - 2010-04-07 : 04:04:39
|
| actually there is a where clause. And i will put where clause at the delete as well. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-07 : 04:07:04
|
quote:
Originally posted by wrz234
actually there is a where clause. And i will put where clause at the delete as well.
then also whats the purpose of that? you're taking some data and then removing them in next step??------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
wrz234
Starting Member
8 Posts |
Posted - 2010-04-07 : 04:13:41
|
| because claimarchive will be stored for old data from table claim. if the claim no is past month. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-07 : 04:15:00
|
quote:
Originally posted by visakh16
quote:
Originally posted by wrz234
actually there is a where clause. And i will put where clause at the delete as well.
then also whats the purpose of that? you're taking some data and then removing them in next step??------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
As you can see the data is copied into another table and then deleted in the source table, hence my question about version of SQL Server because it would be possible to use DELETE with OUTPUT...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
wrz234
Starting Member
8 Posts |
Posted - 2010-04-07 : 04:17:48
|
| the version of sql server is 2005 |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-07 : 04:36:34
|
| oh ok..I see now..Yup thats fine------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Move data