SQL Server 将一个表中的所有行复制到另一个表中，即重复表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2691441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server copy all rows from one table into another i.e duplicate table
提问by iamjonesy
I want to keep a table as history and replace it with an empty one. How can I do this through Management Studio?
我想保留一张表作为历史记录并用空表替换它。如何通过 Management Studio 执行此操作？
回答by froadie
Duplicate your table into a table to be archived:
将您的表复制到要存档的表中：
SELECT * INTO ArchiveTable FROM MyTable
Delete all entries in your table:
删除表中的所有条目：
DELETE * FROM MyTable
回答by Hans Olsson
Don't have sql server around to test but I think it's just:
周围没有 sql server 来测试，但我认为它只是：
insert into newtable select * from oldtable;
回答by Michael Buen
select * into x_history from your_table_here;
truncate table your_table_here;
回答by ntziolis
Either you can use RAW SQL:
您可以使用 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
回答by KM.
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)
回答by Anup Ghosh
This will work:
这将起作用：
select * into DestinationDatabase.dbo.[TableName1] from (
Select * from sourceDatabase.dbo.[TableName1])Temp
