如何在 SQL Server 中同时将数据插入到两个表中?
假设我的表结构如下所示:
Let's say my table structure looks something like this:
CREATE TABLE [dbo].[table1] (
[id] [int] IDENTITY(1,1) NOT NULL,
[data] [varchar](255) NOT NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC)
)
CREATE TABLE [dbo].[table2] (
[id] [int] IDENTITY(1,1) NOT NULL,
[table1_id] [int] NOT NULL,
[data] [varchar](255) NOT NULL,
CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC)
)
第一张表的[id]字段对应第二张表的[table1_id]字段.我想做的是在单个事务中将数据插入到两个表中.现在我已经知道如何通过执行 INSERT-SELECT-INSERT 来做到这一点，如下所示:
The [id] field of the first table corresponds to the [table1_id] field of the second. What I would like to do is insert data into both tables in a single transaction. Now I already know how to do this by doing INSERT-SELECT-INSERT, like this:
BEGIN TRANSACTION;
DECLARE @id [int];
INSERT INTO [table1] ([data]) VALUES ('row 1');
SELECT @id = SCOPE_IDENTITY();
INSERT INTO [table2] ([table1_id], [data]) VALUES (@id, 'more of row 1');
COMMIT TRANSACTION;
这对于您可能只插入几行的小案例来说都很好.但我需要做的是一次插入几十万行，甚至可能是一百万行.数据来自另一个表，所以如果我只是将它插入到一个表中，那就很容易了，我只需要这样做:
That's all good and fine for small cases like that where you're only inserting maybe a handful of rows. But what I need to do is insert a couple hundred thousand rows, or possibly even a million rows, all at once. The data is coming from another table, so if I was only inserting it into a single table, it would be easy, I'd just have to do this:
INSERT INTO [table] ([data])
SELECT [data] FROM [external_table];
但是我将如何做到这一点并将数据拆分为 [table1] 和 [table2]，并且仍然更新 [table2]适当的 [table1_id] 就像我在做的那样?这可能吗?
But how would I do this and split the data into [table1] and [table2], and still update [table2] with the appropriate [table1_id] as I'm doing it? Is that even possible?
推荐答案
试试这个:
insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]
更新:回复:
Denis - 这似乎非常接近我想要做的，但也许您可以为我修复以下 SQL 语句?基本上，[table1] 中的 [data] 和 [table2] 中的 [data] 代表来自 [external_table] 的两个不同/不同的列.您上面发布的语句仅在您希望 [data] 列相同时才有效.
Denis - this seems very close to what I want to do, but perhaps you could fix the following SQL statement for me? Basically the [data] in [table1] and the [data] in [table2] represent two different/distinct columns from [external_table]. The statement you posted above only works when you want the [data] columns to be the same.
INSERT INTO [table1] ([data])
OUTPUT [inserted].[id], [external_table].[col2]
INTO [table2] SELECT [col1]
FROM [external_table]
不可能在 insert 语句中输出外部列，所以我认为你可以这样做
It's impossible to output external columns in an insert statement, so I think you could do something like this
merge into [table1] as t
using [external_table] as s
on 1=0 --modify this predicate as necessary
when not matched then insert (data)
values (s.[col1])
output inserted.id, s.[col2] into [table2]
;
相关文章