问题描述
我有两个表，我想用数据填充它们.这些表是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)
为了复制数据:
我需要将
Source表中的title列复制到Threads表中，并添加一些固定日期和作者用户名它(我希望作者是一些常量字符串，日期是从某个 T-SQL 函数自动生成的DateTime)
I need to copy
titlecolumn fromSourcetable intoThreadstable, and add some fixed date, and author username into it (I want author to be some constant string, and date to be autogeneratedDateTimefrom 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.
这篇关于SQL Server:复杂的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！