• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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,

Open 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

Open in new window

0
Mark01
Asked:
Mark01
3 Solutions
 
Jim P.Commented:
I don't see any insert into the other DB in the code.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Like Jim said, you don't insert into a table, only into table variables. And what help do you expect not posting the specific error messages or unexpected behaviour you can't cope with yourself.

Let's focus on one insert:
INSERT INTO @tblTaskOccurrences
	(TaskOccurrenceID,
	TaskOccurDate,
	TaskOccurStartTime ,
	TaskOccurEndTime
	)
SELECT etDate, etStartTime, etEndTime
FROM tblEmpTimesheet

Open in new window


This has some errors.

You are selecting 3 columns from tblEmpTimesheet, but insert into 4 columns of the destination table. If TaskOccurrenceID is an integer identity field, in which IDs are automatically created, you don't put it into the column list of the destination table of the INSERT.

But: If you migrate data you typically want to keep all IDs already generated. Otherwise your data relations won't work anymore, because foreign keys in child tables would need the newly generated ids. If your old tables are split, and in that process new IDs are generated, that's fine. Otherwise you need to make use of SET IDENTITY_INSERT, to temporarily allow inserting values from the current data into the otherwise readonly field, and then you need to specify a source column, of course. About IDENTITY_INSERT see http://msdn.microsoft.com/de-de/library/ms188059.aspx

To address the real destination table use the multipart name of the tables: server_name.[database_name].[schema_name].object_name. But if you want to move data into another database server, you better use SSIS than just a T-SQL script. I'd suggest you ask a new question about how to migrate a database and do some transformations underway via SSIS then.

Bye, Olaf.
0
 
hnasrCommented:
You may make it simple by using 2 old tables and 2 new tables with few fields each.

Use queries to directly insert into new table from a select from old ones.

In general: for example insert into table H, 3 fields selected from 3 tables A, B, and C.

INSERT INTO H ( id, name, x)
            select  A.a, B.b, C.c  From (A INNER JOIN B ON A.a=B.a) INNER JOIN C ON C.b=B.b;
0
 
Mark01Author Commented:
Thank you hnasr, Jim P and Olaf.
0
 
hnasrCommented:
Welcome!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now