在SQL Server中插入多对多表

This is my Tag table:

这是我的标签表:

CREATE TABLE [dbo].[Tag](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [CreationDate] [datetime] NOT NULL,
    [TagSlug] [nvarchar](max) NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

and this is my Post table:

这是我的邮筒:

CREATE TABLE [dbo].[Post](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](400) NOT NULL,
    [Body] [nvarchar](max) NOT NULL,
    [Summary] [nvarchar](max) NOT NULL,
    [CreationDate] [datetime] NOT NULL,
    [UrlSlug] [nvarchar](max) NOT NULL,
    [Picture] [nvarchar](max) NULL,
    [TagId] [int] NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Post]  WITH CHECK ADD  CONSTRAINT [Post_Tag] FOREIGN KEY([TagId])
REFERENCES [dbo].[Tag] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [Post_Tag]
GO

I just wanna to insert the Id from Tag and PostId from Post into a new table named Post_Tag which is a many to many relation, this is the script of my Post_Tag table:

我只想把Id从标签和PostId从Post插入到一个名为Post_Tag的新表这是一个多到多的关系，这是我的Post_Tag表的脚本:

CREATE TABLE [dbo].[Post_Tag](
    [PostId] [int] NOT NULL,
    [TagId] [int] NOT NULL,

    CONSTRAINT [PK_dbo.Post_Tag] PRIMARY KEY CLUSTERED ([PostId] ASC, [TagId] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Post_Tag]  WITH CHECK 
ADD CONSTRAINT [FK_dbo.Post_Tag_dbo.Post_PostId] 
FOREIGN KEY([PostId]) REFERENCES [dbo].[Post] ([Id])
   ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Post_Tag] CHECK CONSTRAINT [FK_dbo.Post_Tag_dbo.Post_PostId]
GO

ALTER TABLE [dbo].[Post_Tag] WITH CHECK 
ADD CONSTRAINT [FK_dbo.Post_Tag_dbo.Tag_TagId] 
FOREIGN KEY([TagId]) REFERENCES [dbo].[Tag] ([Id])
   ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Post_Tag] CHECK CONSTRAINT [FK_dbo.Post_Tag_dbo.Tag_TagId]
GO

Now, to do that I've tried the below query:

现在，为了做到这一点，我尝试了下面的查询:

insert into [Blog].[dbo].[Post_Tag] (PostId,TagId)
   select [Id] as [PostId] from [OldBlog].[dbo].[Tag]
   select [TagId] from [OldBlog].[dbo].[Post]

but this error appear while running the script:

但是在运行脚本时出现了这个错误:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

INSERT语句的select列表包含的项比INSERT列表要少。选择值的数量必须与插入列的数量匹配。

what's wrong with my query? thanks

我的查询有什么问题?谢谢

2 个解决方案

#1

The 2 select queries are being processed separately. You will have to come up with a way to join [OldBlog].[dbo].[Tag] to [OldBlog].[dbo].[Post] so you can insert fields PostId,TagId into [Blog].[dbo].[Post_Tag] from this new table expression.

两个select查询分别被处理。你必须想出一个加入[OldBlog].[dbo]的方法。[标记][OldBlog]。[dbo]。所以你可以插入字段PostId,TagId into [Blog].[dbo]。[Post_Tag]来自这个新表表达式。

#2

For this, you can use the row number of each row from the two select statements as a link so you can join them and select what you need from both of them.

为此，您可以使用两个select语句中的每一行的行号作为链接，以便您可以连接它们并从它们中选择需要的内容。

SELECT POST.[PostId], TAG.[TagId]
FROM (
select ROW_NUMBER() OVER (ORDER BY [Id])  AS Link, [Id] as [PostId] from [OldBlog].[dbo].[Tag]) AS POST
JOIN (
select ROW_NUMBER() OVER (ORDER BY [TagId]) AS Link, [TagId] from [OldBlog].[dbo].[Post]) AS TAG ON POST.Link = TAG.Link

IMPORTANT NOTE: This is just a means of "forcing" a relationship between tables without any relationship to each other whatsoever. This is indeed a dangerous thing to do because we are forcing a relationship between the tables based on row number and not an actual key. This should only be used if there is no definite expected output or as a last resort if there is no other way to link two or more unrelated tables where the relationship of each selected column don't matter.

重要的注意:这只是一种“强制”表之间的关系而没有任何关系的方法。这确实是一件危险的事情，因为我们是基于行号而不是实际的键来强制表之间的关系。只有在没有明确的预期输出或没有其他方法连接两个或多个不相关的表时，才会使用此方法，因为每个选定列的关系并不重要。