SQL Server:复杂的插入

Now when the Threads row is created, I need to get it's ID, and create new Posts row, which will contain ID of new thread, content from Source.postContent, and some other fixed values

我知道这可能很复杂，但您能否在这里给我一些指导?我怎么做这样的事情?这里的主要问题是需要先创建Threads，然后在Posts 中使用它的ID.

I know that this probably is complicated, but can you maybe give me some guidelines here? How do I do such a thing? The main issue here, is the need of creating Threads first, and then using it's ID in Posts.

推荐答案

请尝试使用以下代码片段.

Please try with the below code snippet.

创建表格并添加虚拟数据

/****** Object:  Table [dbo].[Threads]    Script Date: 11/06/2013 13:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Threads](
    [ThreadID] [int] IDENTITY(1,1) NOT NULL,
    [ThreadTitle] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Threads] PRIMARY KEY CLUSTERED 
(
    [ThreadID] 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].[SourceTable]    Script Date: 11/06/2013 13:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SourceTable](
    [SourceTableID] [int] IDENTITY(1,1) NOT NULL,
    [SourceTitle] [nvarchar](50) NULL,
    [SourceContent] [nvarchar](50) NULL,
 CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED 
(
    [SourceTableID] 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].[Posts]    Script Date: 11/06/2013 13:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Posts](
    [PostID] [int] IDENTITY(1,1) NOT NULL,
    [PostContent] [nvarchar](50) NULL,
    [ThreadID] [int] NOT NULL,
 CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED 
(
    [PostID] 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:  ForeignKey [FK_Posts_Threads]    Script Date: 11/06/2013 13:57:51 ******/
ALTER TABLE [dbo].[Posts]  WITH CHECK ADD  CONSTRAINT [FK_Posts_Threads] FOREIGN KEY([ThreadID])
REFERENCES [dbo].[Threads] ([ThreadID])
GO
ALTER TABLE [dbo].[Posts] CHECK CONSTRAINT [FK_Posts_Threads]
GO


SET IDENTITY_INSERT [dbo].[SourceTable] ON
INSERT [dbo].[SourceTable] ([SourceTableID], [SourceTitle], [SourceContent]) VALUES (1, N'blog1', N'blogdesc1')
INSERT [dbo].[SourceTable] ([SourceTableID], [SourceTitle], [SourceContent]) VALUES (2, N'blog2', N'blogdesc2')
INSERT [dbo].[SourceTable] ([SourceTableID], [SourceTitle], [SourceContent]) VALUES (3, N'blog3', N'blogdesc3')
SET IDENTITY_INSERT [dbo].[SourceTable] OFF

查询在表中插入数据

CREATE TABLE #SummaryOfChanges(actionType NVARCHAR(50),ThreadID NVARCHAR(40),SourceContent NVARCHAR(40))

MERGE INTO  Threads AS d
USING (SELECT SourceTableID,SourceTitle,SourceContent FROM SourceTable) AS s
ON 1 = 2 
WHEN NOT MATCHED THEN
    INSERT (ThreadTitle)
    VALUES (s.SourceTitle)
    OUTPUT $action, Inserted.ThreadID, s.SourceContent INTO #SummaryOfChanges;

MERGE INTO  Posts AS d
USING (SELECT ThreadID,SourceContent FROM #SummaryOfChanges) AS s
ON d.ThreadID = s.ThreadID 
WHEN MATCHED THEN 
            UPDATE SET  d.PostContent= s.SourceContent
WHEN NOT MATCHED THEN
    INSERT (ThreadID,PostContent)
    VALUES (ThreadID,s.SourceContent);

DROP TABLE #SummaryOfChanges

如果有任何问题，请告诉我.

Let me know if any concern.

这篇关于SQL Server:复杂的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！