T-SQL Commands

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

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


The error message shown below occurs after the following query is executed:

-- 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

Open in new window


Error:
Msg 120, Level 15, State 1, Line 8
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


The code shown below is incomplete. I need help with the query.

-- 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?
 
PortletPaulfreelancerCommented:
@Mark01

If I placed you into a car, then painted all windows with black paint. How successful would you be driving that car?


You have placed us into your database (via a set of queries) but have painted every other fact about the database black. We cannot see the data, we cannot see any error messages, we cannot complete the task of helping you unless you help us. And we don't really have an appreciation for what you are aiming at either.

This question is a repeat also. (So I'm afraid there's not a lot of learning being demonstrated.)

There is A WAY to move forward with this question.

1. PROVIDE SAMPLES OF THE DATA

By table, not queries using joins. Private data such as names should be altered. Do not use images for this.

2. PROVIDE EXPECTED RESULTS

based on the sample data, manually re-arrange it to demonstrate what the outcome will be.
You could spend the next week trying to describe what it is you are attempting and still not have us understand.
Or, you could paste some data into Excel and upload it, and there's a good chance we can help.

-------------

UNION QUERY 101

This will never work as given to us:
INSERT INTO @tblEmployeeTasks
      (TaskOccurrenceID,
      etEmpTimesheetID ,
      TrustID ,
      mstsTopicID
      )
--Existing Emp Timesheet table
SELECT etDate, etStartTime, etEndTime --<< 3 columns
FROM tblEmpTimesheet
UNION ALL
--New Task Occurrences table
SELECT TaskOccurrenceID,  --<< 1 column and trailing comma, black paint here
FROM tblTaskOccurrences
UNION ALL
--Existing Trusts table
SELECT TrustID,   --<< 1 column and trailing comma, black paint here
FROM tblTrusts
UNION ALL
--Existing Topics table
SELECT mstsTopicID,   --<< 1 column and trailing comma, black paint here
FROM tblmtsTopics

This would work:

select A, B, C from Atable UNION ALL
select D, E, F from Btable UNION ALL
select G, H, I from Ctable
Because there a the same number of columns (3) in each part of the union.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
INSERT INTO @tblTaskOccurrences
	(TaskOccurrenceID,
	TaskOccurDate,
	TaskOccurStartTime ,
	TaskOccurEndTime
	)
SELECT etDate, etStartTime, etEndTime
FROM tblEmpTimesheet

Open in new window

>The select list for the INSERT statement contains fewer items than the insert list.
In the above code block I'm counting three columns in the SELECT, and four columns in the INSERT INTO, with the oddball being TaskOccurenceID.   Either populate it with something in the SELECT clause, or remove it from the INSERT INTO clause.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, in the UNION ALL block, all SELECT clauses in the UNION must match.
Explain in more detail what you're trying to pull off there.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Mark01Author Commented:
Jim Horn, I ran the query shown below and received a "(478 row(s) affected)" message. However, when I run the select * from tblTaskOccurrences query, it shows an empty table. Why is the table empty?

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
	(TaskOccurDate,
	TaskOccurStartTime ,
	TaskOccurEndTime
	)
SELECT etDate, etStartTime, etEndTime
FROM tblEmpTimesheet

Open in new window



One of the purposes of the query shown below is to copy existing data into the new Employee Tasks table. The existing Emp Timesheet table contains the employee  start times and end times. I am trying to copy the primary key (etEmpTimesheetID) for each record into the new Employee Tasks table.

I am also trying to copy the primary key for the existing Trusts and Topics tables into the new Employee Tasks table.

Finally, I am trying to copy the primary key for 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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I ran the query shown below and received a "(478 row(s) affected)" message.
>when I run the select * from tblTaskOccurrences
@tblTaskOccurrences is only in memory for the scope of the T-SQL, and then it gets destroyed.
@tblTaskOccurrences is not the same as tblTaskOccurrences (without the prefix), which will be a physical table.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Why don't you follow what already was said in http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28494151.html

I already informed you about the now solved error "The select list for the INSERT statement contains fewer items than the insert list" there. Quote:
You are selecting 3 columns from tblEmpTimesheet, but insert into 4 columns of the destination table
That of course can't work.

And I already said if you want to move data to another table, you have to use the real destination table and not a table variable, Use the multipart name of the tables: server_name.[database_name].[schema_name].object_name.

What you do now is fine for testing purposes, but it doesn't move data to somewhere. Defining a table variable  @tblEmpTimesheet is not making the real table tblEmpTimesheet the destination, but the variable, memory. So after all your code ran the data is in a table variable @tblEmpTimesheet in memory, which then is released. If you defined a new table tblEmpTimesheet in the same database already, then just skip the variable creation, if you don't have a destination table you can't define it via a variable, you use CREATE TABLE for creating a table.

Bye, Olaf.
0
 
awking00Commented:
If you really want to get some help, do this.
Show the column name and datatype with five values for each of your existing tables, then show the values you would like to see in your newly created tables noting their source table. Table structure, sample data and expected output is more valuable to the experts than any literal explanation of the intent could ever be.
0
 
Mark01Author Commented:
Thank you, Jim Horn, Olaf_Doschke, PortletPaul and awking00.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.