T-SQL Commands

I’m trying to write T-SQL command(s) that will copy data from a database with some normalized tables to new database in which all of the tables are normalized.

The purpose of the employee database is to maintain the data used to track information about the work performed by the employees and the supervisor.

I need help writing the query that will copy data from the existing EmpTimesheet table into the new Task Occurrences table. The query will also copy data from the new Task Occurrences table and the existing Trusts and Topics tables into the new Employee Tasks table. Any help with the code will be greatly appreciated.

TABLES
The code shown below creates the tables.

-- Tables in existing database
CREATE TABLE [dbo].[tblEmployees](
	[EmployeeID] [int] NOT NULL,
	[eFirstName] [nvarchar](50) NULL,
	[eLastName] [nvarchar](50) NULL
---------------------------------------------
CREATE TABLE [dbo].[tblEmpTimesheet](
	[etEmpTimesheetID] [int] NOT NULL,
	[etDate] [datetime] NULL,
	[etStartTime] [datetime] NULL,
	[etEndTime] [datetime] NULL,
	[mhEffort] [nvarchar](max) NULL,
	[mstsTopicID] [int] NULL,
	[EmployeeID] [int] NULL
----------------------------------------------
CREATE TABLE [dbo].[tblmtsSubjects](
	[mtsSubjectID] [int] NOT NULL,
	[mtsSubject] [nvarchar](50) NULL
----------------------------------------------
CREATE TABLE [dbo].[tblmtsTopics](
	[mstsTopicID] [int] NOT NULL,
	[tblmtsTopic] [nvarchar](50) NULL,
	[mtsSubjectID] [int] NULL
---------------------------------------------------
CREATE TABLE [dbo].[tblTmshtAccomplshmts](
	[TmshtAccomplshmtID] [int] NOT NULL,
	[tmshAcName] [nvarchar](50) NULL,
	[tmshAccomplshmt] [nvarchar](150) NULL,
	[tmshDetailName_1] [nvarchar](50) NULL,
	[tmshDetail_1] [nvarchar](max) NULL,
	[tmshDetailName_2] [int] NULL,
	[tmshDetail_2] [nvarchar](max) NULL
--------------------------------------------
CREATE TABLE [dbo].[tblTrusts](
	[TrustID] [int] NOT NULL,
	[Trust] [nvarchar](50) NULL
------------------------------------------
CREATE TABLE [dbo].[tblEmpTmshtAccomplshmts_Tmsht_Link](
	[TmshtAccomplshmtID] [int] NULL,
	[etEmpTimesheetID] [int] NULL,
	[TrustID] [int] NULL,
	[mstsTopicID] [int] NULL
	 ------------------------------------
	 -- Tables in new database
	 
	 CREATE TABLE [dbo].[tblTasks](
	[TaskID] [int] NOT NULL,
	[TaskName] [nvarchar](50) NULL,
	[TaskDescription] [nvarchar](150) NULL
	------------------------------------------
	CREATE TABLE [dbo].[tblEmployeeTasks](
	[TmshtAccomplshmtID] [int] NULL,
	[etEmpTimesheetID] [int] NULL,
	[TrustID] [int] NULL,
	[mstsTopicID] [int] NULL
		------------------------------------------
	CREATE TABLE [dbo].[tblTaskOccurrences](
	[TaskOccurrenceID] [int] NULL,
	[TaskOccurDate] [datetime] NULL,
	[TaskOccurStartTime] [datetime] NULL,
	[TaskOccurEndTime] [datetime] NULL,

Select allOpen in new window

QUERIES
The code shown below contains comments that indicate the source and destination tables.

-- Copy data into the the new Task Occurrences table. Copy data from the existing EmpTimesheet table.
DECLARE @tblTaskOccurrences TABLE (
	[TaskOccurrenceID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[TaskOccurDate] [datetime] NULL,
	[TaskOccurStartTime] [datetime] NULL,
	[TaskOccurEndTime] [datetime] NULL
)

INSERT INTO @tblTaskOccurrences
	(TaskOccurrenceID,
	TaskOccurDate,
	TaskOccurStartTime ,
	TaskOccurEndTime
	)
SELECT etDate, etStartTime, etEndTime
FROM tblEmpTimesheet


-- Copy data into the new Employee Tasks table. Copy data from the existing EmpTimesheet, Trusts and Topics tables into the new Employee Tasks table. Copy data from the new Task Occurrences table into the new Employee Tasks table.
DECLARE @EmployeeTasks TABLE (
	[TaskOccurrenceID] [int] NULL,
	[etEmpTimesheetID] [int] NULL,
	[TrustID] [int] NULL,
	[mstsTopicID] [int] NULL
)

INSERT INTO @tblEmployeeTasks
	(TaskOccurrenceID,
	etEmpTimesheetID ,
	TrustID ,
	mstsTopicID
	)
--Existing Emp Timesheet table
SELECT etDate, etStartTime, etEndTime
FROM tblEmpTimesheet
UNION ALL
--New Task Occurrences table
SELECT TaskOccurrenceID, 
FROM tblTaskOccurrences
UNION ALL
--Existing Trusts table
SELECT TrustID, 
FROM tblTrusts
UNION ALL
--Existing Topics table
SELECT mstsTopicID, 
FROM tblmtsTopics

Select allOpen in new window