我有两个表，我想填充数据。 这些表是Threads和Posts 。 另外我有一个名为表Source包含的数据。
Threads和Posts中含有大量的列被填满，所以我将他们在这里没有粘贴简单起见，但其中大多数是可以对一些固定值。 Source表包含以下几列- title （进入Threads.title ）， postContent （进入Posts.content ）
为了复制数据：
我需要复制title列从Source表到Threads表，并添加一些固定的日期和作者名到它（我想作者是一些常量字符串和日期将自动生成DateTime从一些T-SQL函数）
现在，当Threads被创建的行，我需要得到它的ID，并创造新的Posts排，其中将包含ID新的线程，从内容Source.postContent ，和一些其他的固定值
我知道，这可能是复杂的，但你也许给我一些指导吗？ 我该怎么做这样的事？ 这里的主要问题，是创造的需要Threads ，然后再使用它在ID Posts 。
请下面的代码片段尝试。
创建表并添加虚拟数据
/****** 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
让我知道如果任何问题。
