sql server 2000, moving a record (line) from one table to another 1

[pssheba]

Hi !
Could any one tell me me please how do i make a copy of a line in a table, paste it to another table and then delete it from its old table ?
Thanks !

 

[bborissov]

You must perform two actions:
1. Insert the record(s) into new table
2. Delete record(s) from old one:

-- 1. Insert the record(s) into new table
--- The example below assume that these two tables
--- have the exact same structure
INSERT INTO NewTable
SELECT * FROM OldTable WHERE .....

DELETE FROM OldTable WHERE .....

--- BOTH where clauses must be identical

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[pssheba]

Thanks a lot bborissov but could i ask you one more ignorant's questio ?
How do i copy a table's structure into a new one i want to create ?
Thanks again...

 

[bborissov]

If you don't have the NewTable you could use this:

SELECT * INTO NewTable FROM OldTable WHERE ....

That would create the newtable with the same structure as Old one. But if the NewTable already exists you must use my first suggestiion, becuase this would raise an error. So if you do this as routine you must check of the NewTable exists:

IF OBJECT_ID('DataBaseName..NewTable') IS NULL
   BEGIN
       --- NewTable does not exists
       SELECT * INTO NewTable FROM OldTable
                WHERE ......
   END
ELSE
   BEGIN
       --- NewTable already exists
      INSERT INTO NewTable
      SELECT * FROM OldTable WHERE .....
   END


---- Delete the records
DELETE FROM OldTable WHERE .....

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP