I want to keep a table as history and replace it with an empty one. How can I do this through the management studio?
--Jonesy
A:
Don't have sql server around to test but I think it's just:
insert into newtable select * from oldtable;
ho1
2010-04-22 14:02:17
nice one! but i never trust the order of the fields ;)
ntziolis
2010-04-22 14:03:55
If there was any place where "SELECT *" should be used its in a query like this... It makes a copy of the table without having to know what's in it.
Robin Day
2010-04-22 14:08:10
@Robin - I agree that a * is a good choice here, **IF** the order of fields is the same ;)
ntziolis
2010-04-22 14:10:42
If you do "insert into ... select * from..." then the order is important. If you use the "select * into ... from ..." it creates the table for you so column order isn't important. Though of course, using that approach the table must not exist beforehand.
GalacticCowboy
2010-04-22 14:23:14
Well, the question did say duplicate table, so I assumed he meant exact copy.
ho1
2010-04-22 14:27:08
A:
Either you can use RAW SQL:
INSERT INTO DEST_TABLE (Field1, Field2)
SELECT Source_Field1, Source_Field2
FROM SOURCE_TABLE
Or use the wizard:
- Right Click on the Database -> Tasks -> Export Data
- Select the source/target Database
- Select source/target table and fields
- Copy the data
Then execute:
TRUNCATE TABLE SOURCE_TABLE
ntziolis
2010-04-22 14:03:01
+1
A:
select * into x_history from your_table_here;
truncate table your_table_here;
Michael Buen
2010-04-22 14:04:48
A:
try this single command to both delete and insert the data:
DELETE MyTable
OUTPUT DELETED.Col1, DELETED.COl2,...
INTO MyBackupTable
working sample:
--set up the tables
DECLARE @MyTable table (col1 int, col2 varchar(5))
DECLARE @MyBackupTable table (col1 int, col2 varchar(5))
INSERT INTO @MyTable VALUES (1,'A')
INSERT INTO @MyTable VALUES (2,'B')
INSERT INTO @MyTable VALUES (3,'C')
INSERT INTO @MyTable VALUES (4,'D')
--single command that does the delete and inserts
DELETE @MyTable
OUTPUT DELETED.Col1, DELETED.COl2
INTO @MyBackupTable
--show both tables final values
select * from @MyTable
select * from @MyBackupTable
OUTPUT:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
col1 col2
----------- -----
(0 row(s) affected)
col1 col2
----------- -----
1 A
2 B
3 C
4 D
(4 row(s) affected)
KM
2010-04-22 14:09:29