我如何在SQL Server中同时将数据插入到两个表中？ [英] How can I INSERT data into two tables simultaneously in SQL Server?

问题描述

  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] 字段对应于<$ c $> 
 
 $ c> [table1_id] 第二个字段。我想要做的是在一个事务中将数据插入到两个表中。现在我已经知道如何通过执行INSERT-SELECT-INSERT来做到这一点，如下所示： 
 
 
  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 of 1'）; 
 COMMIT TRANSACTION; 
  
对于那些只插入一些行的小案例来说， 。但是我需要做的是一次插入几十万行甚至一百万行。数据来自另一个表，所以如果我只是将它插入到一个表中，这将是容易的，我只需要这样做：
 
 <$ p $ INSERT INTO [table]（[data]）
 SELECT [data] FROM [external_table]; 
  

但是我怎么做到这一点，并将数据分成 [table1] 和 [table2] ，并用相应的<$ c $更新 [table2] c> [table1_id] 我正在做这件事吗？甚至有可能吗？

解决方案

试试这个：

  insert into [table]（[data]）
 output inserted.id，inserted.data into table2 
 select [data] from [external_table] 
  
 
 更新：回复： 
 
 
 
 
 Denis  - 这看起来与我想要做的非常接近，但也许你可以为我修复下面的SQL语句？基本上[table1]中的[data]和[table2]中的[data]代表[external_table]中的两个不同/不同的列。您在上面发表的声明只有当您希望[数据]列是相同的。  
 
 
 
 
  INSERT INTO [table1]（[data]）
 OUTPUT [inserted] 。[col2] 
 INTO [table2] SELECT [col1] 
 FROM [external_table] 
  
在 insert 语句中输出外部列是不可能的，所以我认为你可以这样做
 
 
 pre $ lt; code>使用[external_table]作为s $ b $ 1合并到[table1] t 
 1 = 0  - 根据需要修改此谓词
当不匹配，然后插入（数据）
值（s。[col1]）$ ​​b $ b输出inserted.id，s。[col2]到[table2] 
; 
  

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)
)

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];

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?

解决方案

Try this:

insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]

UPDATE: Re:

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] 

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]
;

这篇关于我如何在SQL Server中同时将数据插入到两个表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！