SQL Server:复杂的插入

2021-09-10 00:00:00 sql tsql sql-server

我有两个表，我想用数据填充它们.这些表是Threads 和Posts.我还有一个名为 Source 的表，其中包含数据.

I have two tables, which I want to fill with data. Those tables are Threads and Posts. Also I have a table called Source which contains data.

Threads 和 Posts 包含很多需要填写的栏目，为了简单起见我就不贴在这里了，但大部分都可以做一些固定价值.Source 表包含以下列 - title(进入 Threads.title)，postContent(进入 Posts.content)

Threads and Posts contain a lot of columns to be filled, so I will not paste them here for the sake of simplicity, but most of them can be some fixed value. Source table contains following columns - title (goes into Threads.title), postContent (goes into Posts.content)

为了复制数据:

  1. 我需要将 Source 表中的 title 列复制到 Threads 表中，并添加一些固定日期和作者用户名它(我希望作者是一些常量字符串，日期是从某个 T-SQL 函数自动生成的 DateTime)

  1. I need to copy title column from Source table into Threads table, and add some fixed date, and author username into it (I want author to be some constant string, and date to be autogenerated DateTime from some T-SQL function)

现在，当创建 Threads 行时，我需要获取它的 ID，并创建新的 Posts 行，其中将包含 ID，来自Source.postContent的内容，以及其他一些固定值

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.

相关文章