問題描述
我有兩個表，我想用數據填充它們.這些表是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:復雜的插入的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！