如何将表复制到另一个表？

I have a table with 1 000 000 records:

我有一张包含1 000 000条记录的表格：

CREATE TABLE [dbo].[x2](
    [session_id] [uniqueidentifier] NOT NULL,
    [node_id] [uniqueidentifier] NOT NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_x2] PRIMARY KEY CLUSTERED 
(
    [id] ASC
));

I need to replace the field

我需要更换该字段

[id] [int] IDENTITY(1,1)

with

同

[id] [bigint] IDENTITY(1,1)

But all data (including id values) should be copied to the new table and id should be IDENTITY but bigint.

但是所有数据（包括id值）都应该复制到新表中，id应该是IDENTITY但是bigint。

I have created the new table

我创建了新表

CREATE TABLE [dbo].[x2_new](
    [session_id] [uniqueidentifier] NOT NULL,
    [node_id] [uniqueidentifier] NOT NULL,
    [id] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_x2_new] PRIMARY KEY CLUSTERED 
(
    [id] ASC
));

and tried to copy data:

并试图复制数据：

insert into x2_new(session_id,node_id,id)
select session_id,node_id,id from x2;

But it is slow. How to copy data to the new table faster?

但它很慢。如何更快地将数据复制到新表？

2 个解决方案

#1

In that case.. you need to do an 'identity_insert OFF' on your destination and insert with your script.. try setting NoLock too.. but is this a one time activity or a repetitive one?

在这种情况下..你需要在你的目的地做一个'identity_insert OFF'并插入你的脚本..尝试设置NoLock ..但这是一次性活动还是重复活动？

#2

If both your tables are in same databse, why don't you use Import Data Task feature , select the columns and run the SSIS package immediately..

如果两个表都在相同的数据库中，为什么不使用“导入数据任务”功能，请选择列并立即运行SSIS包。

#1