Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

T-SQL Commands

Posted on 2014-08-14
8
Medium Priority
?
443 Views
Last Modified: 2014-08-15
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

0
Comment
Question by:Mark01
8 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 total points
ID: 40261495
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40261497
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
 

Author Comment

by:Mark01
ID: 40261624
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 total points
ID: 40261646
>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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 800 total points
ID: 40262305
@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
 
LVL 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 400 total points
ID: 40262822
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 400 total points
ID: 40263254
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
 

Author Closing Comment

by:Mark01
ID: 40263542
Thank you, Jim Horn, Olaf_Doschke, PortletPaul and awking00.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question