Mark01
asked on
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.
QUERIES
The code shown below contains comments that indicate the source and destination tables.
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,
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Welcome!
ASKER