I have a rather large (many gigabytes) table of data in SQL Server that I wish to move to a table in another database on the same server.
我在SQL Server中有一个相当大(很多千兆字节)的数据表,我希望将其移动到同一服务器上另一个数据库中的表。
The tables are the same layout.
表格是相同的布局。
What would be the most effecient way of going about doing this?
这样做最有效的方法是什么?
This is a one off operation so no automation is required.
这是一次性操作,因此不需要自动化。
Many thanks.
5 个解决方案
#1
6
If it is a one-off operation, why care about top efficiency so much?
如果是一次性操作,为什么要关注最高效率呢?
SELECT * INTO OtherDatabase..NewTable FROM ThisDatabase..OldTable
or
INSERT OtherDatabase..NewTable
SELECT * FROM ThisDatabase..OldTable
...and let it run over night. I would dare to say that using SELECT/INSERT INTO on the same server is not far from the best efficiency you can get anyway.
......让它过夜。我敢说,在同一台服务器上使用SELECT / INSERT INTO并不是你可以获得的最佳效率。
#2
2
Or you could use the "SQL Import and Export Wizard" found under "Management" in Microsoft SQL Server Management Studio.
或者,您可以使用Microsoft SQL Server Management Studio中“管理”下的“SQL导入和导出向导”。
#3
2
I'd go with Tomalak's answer.
我会选择Tomalak的答案。
You might want to temporarily put your target database into bulk-logged recovery mode before executing a 'select into' to stop the log file exploding...
您可能希望暂时将目标数据库置于批量记录恢复模式,然后执行'select into'以停止日志文件爆炸...
#4
1
If it's SQL Server 7 or 2000 look at Data Transformation Services (DTS). For SQL 2005 and 2008 look at SQL Server Integration Services (SSIS)
如果是SQL Server 7或2000,请查看数据转换服务(DTS)。对于SQL 2005和2008,请查看SQL Server Integration Services(SSIS)
#5
1
Definitely put the target DB into bulk-logged mode. This will minimally log the operation and speed it up.
绝对将目标数据库置于批量记录模式。这将最小化记录操作并加快速度。
#1
6
If it is a one-off operation, why care about top efficiency so much?
如果是一次性操作,为什么要关注最高效率呢?
SELECT * INTO OtherDatabase..NewTable FROM ThisDatabase..OldTable
or
INSERT OtherDatabase..NewTable
SELECT * FROM ThisDatabase..OldTable
...and let it run over night. I would dare to say that using SELECT/INSERT INTO on the same server is not far from the best efficiency you can get anyway.
......让它过夜。我敢说,在同一台服务器上使用SELECT / INSERT INTO并不是你可以获得的最佳效率。
#2
2
Or you could use the "SQL Import and Export Wizard" found under "Management" in Microsoft SQL Server Management Studio.
或者,您可以使用Microsoft SQL Server Management Studio中“管理”下的“SQL导入和导出向导”。
#3
2
I'd go with Tomalak's answer.
我会选择Tomalak的答案。
You might want to temporarily put your target database into bulk-logged recovery mode before executing a 'select into' to stop the log file exploding...
您可能希望暂时将目标数据库置于批量记录恢复模式,然后执行'select into'以停止日志文件爆炸...
#4
1
If it's SQL Server 7 or 2000 look at Data Transformation Services (DTS). For SQL 2005 and 2008 look at SQL Server Integration Services (SSIS)
如果是SQL Server 7或2000,请查看数据转换服务(DTS)。对于SQL 2005和2008,请查看SQL Server Integration Services(SSIS)
#5
1
Definitely put the target DB into bulk-logged mode. This will minimally log the operation and speed it up.
绝对将目标数据库置于批量记录模式。这将最小化记录操作并加快速度。