SQL Server将一个表中的所有行复制到另一个i中。e复制表

I want to keep a table as history and replace it with an empty one. How can I do this through Management Studio?

我想保留一个表作为历史，并用一个空表替换它。我如何通过管理工作室来实现这一点?

6 个解决方案

#1

Duplicate your table into a table to be archived:

将您的表复制到要存档的表中:

SELECT * INTO ArchiveTable FROM MyTable

Delete all entries in your table:

删除表中的所有条目:

DELETE * FROM MyTable

#2

select * into x_history from your_table_here;
truncate table your_table_here;

#3

Don't have sql server around to test but I think it's just:

不要让sql服务器来测试，但我认为它只是:

insert into newtable select * from oldtable;

#4

Either you can use RAW SQL:

您可以使用原始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

#5

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)

#6

-2

This will work:

这将工作:

select * into DestinationDatabase.dbo.[TableName1] from (
Select * from sourceDatabase.dbo.[TableName1])Temp

#1