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

Mark01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.