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 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)
In order to copy data:
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)
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
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.
Create Table and add dummy data
/****** 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
Query to insert data in table
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.
