代码之家 › 专栏 › 技术社区 › Álvaro García

有没有办法一次插入所有关系?

tsql sql-server

Álvaro García · 技术社区 · 6 年前

我有一个n:n关系表:

MyTable(IDTable1, IDTable2)

我必须收集ID,一个收集ID来自表1,另一个收集ID来自表2。我必须将集合1中的每个ID与集合2中的所有ID关联起来。

是否可以用一个T-SQL查询来完成,或者我需要为每个关系创建一个T-SQL查询?

谢谢。

编辑:我添加脚本来生成3个表。表01有3行,表02有6行,n:n表有6条记录。

USE [Dummy01]
GO
/****** Object:  Table [dbo].[Table01]    Script Date: 15/07/2018 16:43:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table01](
    [IDTable01] [bigint] IDENTITY(1,1) NOT NULL,
    [Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Tabl01] PRIMARY KEY CLUSTERED 
(
    [IDTable01] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Table01_Table02_Relationship]    Script Date: 15/07/2018 16:43:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table01_Table02_Relationship](
    [IDTable01] [bigint] NOT NULL,
    [IDTable02] [bigint] NOT NULL,
 CONSTRAINT [PK_Table01_Table02_Relationship] PRIMARY KEY CLUSTERED 
(
    [IDTable01] ASC,
    [IDTable02] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Table02]    Script Date: 15/07/2018 16:43:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table02](
    [IDTable02] [bigint] IDENTITY(1,1) NOT NULL,
    [Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Table02] PRIMARY KEY CLUSTERED 
(
    [IDTable02] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Table01] ON 

INSERT [dbo].[Table01] ([IDTable01], [Description]) VALUES (1, N'Description 01')
INSERT [dbo].[Table01] ([IDTable01], [Description]) VALUES (2, N'Description 02')
INSERT [dbo].[Table01] ([IDTable01], [Description]) VALUES (3, N'Description 03')
INSERT [dbo].[Table01] ([IDTable01], [Description]) VALUES (4, N'Description 04')
SET IDENTITY_INSERT [dbo].[Table01] OFF
INSERT [dbo].[Table01_Table02_Relationship] ([IDTable01], [IDTable02]) VALUES (2, 4)
INSERT [dbo].[Table01_Table02_Relationship] ([IDTable01], [IDTable02]) VALUES (2, 5)
INSERT [dbo].[Table01_Table02_Relationship] ([IDTable01], [IDTable02]) VALUES (2, 6)
INSERT [dbo].[Table01_Table02_Relationship] ([IDTable01], [IDTable02]) VALUES (3, 4)
INSERT [dbo].[Table01_Table02_Relationship] ([IDTable01], [IDTable02]) VALUES (3, 5)
INSERT [dbo].[Table01_Table02_Relationship] ([IDTable01], [IDTable02]) VALUES (3, 6)
SET IDENTITY_INSERT [dbo].[Table02] ON 

INSERT [dbo].[Table02] ([IDTable02], [Description]) VALUES (1, N'Description 01')
INSERT [dbo].[Table02] ([IDTable02], [Description]) VALUES (2, N'Description 02')
INSERT [dbo].[Table02] ([IDTable02], [Description]) VALUES (3, N'Description 03')
INSERT [dbo].[Table02] ([IDTable02], [Description]) VALUES (4, N'Description 04')
INSERT [dbo].[Table02] ([IDTable02], [Description]) VALUES (5, N'Description 05')
INSERT [dbo].[Table02] ([IDTable02], [Description]) VALUES (6, N'Description 06')
INSERT [dbo].[Table02] ([IDTable02], [Description]) VALUES (7, N'Description 07')
SET IDENTITY_INSERT [dbo].[Table02] OFF

1 回复 | 直到 6 年前

Lukasz Szozda 6 年前

您可以通过单个查询来实现它:

INSERT INTO MyTable(IdTable1, IDTable2)
SELECT t1.IDTable1, t2.IDTable2
FROM table1 t1
CROSS JOIN table2 t2