Creating T-SQL Table

jo15765 · #1 (**permalink**) 01-28-13, 17:36

In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How would one of you guru's recommend creating 1 table, that is composed of 3 other tables?

I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.

Pat Phelan · #2 (**permalink**) 01-28-13, 17:56

Please show us the structure of the existing tables so that we can help you better. You can get that information by executing the following script, assuming that your tables are named jo_demo_1, jo_demo_2, and jo_demo_3.

Code:

EXECUTE sp_help 'jo_demo_1'
EXECUTE sp_help 'jo_demo_2'
EXECUTE sp_help 'jo_demo_3'

Execute these commands and send the output to text. Save the resulting text into a file, and post it here for us to review.

-PatP

jo15765 · #3 (**permalink**) 01-28-13, 19:18

When I attempt to execute those commands I keep getting an error of The object 'dbo.tblEmpJoseph' does not exist

If you are needing to know what data types the tables contain, they are all either int (2 fields), datetime (3 fields) and varchar (remaining fields).

Pat Phelan · #4 (**permalink**) 01-28-13, 22:22

My first guess is that you're trying to execute the commands in the wrong database. That's really easy to do when you're just getting started.

I'll give you same sample tables and data. Play with them, see what works for you.

Code:

--  Build the source tables

CREATE TABLE #a (
   ai1		INT			NULL	
,  ai2		INT			NULL
,  ad1		DATETIME	NULL
,  ad2		DATETIME	NULL
,  ad3		DATETIME	NULL
,  ac1		VARCHAR(9)	NULL
,  ac2		VARCHAR(9)	NULL
   )

CREATE TABLE #b (
   bi1		INT			NULL
,  bi2		INT			NULL
,  bd1		DATETIME	NULL
,  bd2		DATETIME	NULL
,  bd3		DATETIME	NULL
,  bc1		VARCHAR(9)	NULL
,  bc2		VARCHAR(9)	NULL
   )

CREATE TABLE #c (
   ci1		INT			NULL
,  ci2		INT			NULL
,  cd1		DATETIME	NULL
,  cd2		DATETIME	NULL
,  cd3		DATETIME	NULL
,  cc1		VARCHAR(9)	NULL
,  cc2		VARCHAR(9)	NULL
   )

--  Populate the source tables

INSERT INTO #a
   SELECT n, 100 + n, GetDate(), DateAdd(minute, n, GetDate()), DateAdd(minute, 10 + n, GetDate()), Char(64 + n), Char(96 + n)
      FROM (SELECT number AS n FROM master.dbo.spt_values AS v WHERE 'P' = v.type) AS z
	  WHERE  z.n BETWEEN  1 AND  5

INSERT INTO #b
   SELECT n, 100 + n, GetDate(), DateAdd(minute, n, GetDate()), DateAdd(minute, 10 + n, GetDate()), Char(64 + n), Char(96 + n)
      FROM (SELECT number AS n FROM master.dbo.spt_values AS v WHERE 'P' = v.type) AS z
	  WHERE  z.n BETWEEN 11 AND 25

INSERT INTO #c
   SELECT n, 100 + n, GetDate(), DateAdd(minute, n, GetDate()), DateAdd(minute, 10 + n, GetDate()), Char(64 + n), Char(96 + n)
      FROM (SELECT number AS n FROM master.dbo.spt_values AS v WHERE 'P' = v.type) AS z
	  WHERE  z.n BETWEEN 21 AND 25

--  Build and populate the wide way

CREATE TABLE #ww (
   ai1		INT			NULL	
,  ai2		INT			NULL
,  ad1		DATETIME	NULL
,  ad2		DATETIME	NULL
,  ad3		DATETIME	NULL
,  ac1		VARCHAR(9)	NULL
,  ac2		VARCHAR(9)	NULL
,  bi1		INT			NULL
,  bi2		INT			NULL
,  bd1		DATETIME	NULL
,  bd2		DATETIME	NULL
,  bd3		DATETIME	NULL
,  bc1		VARCHAR(9)	NULL
,  bc2		VARCHAR(9)	NULL
,  ci1		INT			NULL
,  ci2		INT			NULL
,  cd1		DATETIME	NULL
,  cd2		DATETIME	NULL
,  cd3		DATETIME	NULL
,  cc1		VARCHAR(9)	NULL
,  cc2		VARCHAR(9)	NULL
   )

INSERT INTO #ww (ai1, ai2, ad1, ad2, ad3, ac1, ac2)
   SELECT ai1, ai2, ad1, ad2, ad3, ac1, ac2
      FROM #a

INSERT INTO #ww (bi1, bi2, ad1, bd2, bd3, bc1, bc2)
   SELECT bi1, bi2, bd1, bd2, bd3, bc1, bc2
      FROM #b

INSERT INTO #ww (ci1, ci2, cd1, cd2, cd3, cc1, cc2)
   SELECT ci1, ci2, cd1, cd2, cd3, cc1, cc2
      FROM #c

--  Build and populate the narrow way

CREATE TABLE #nn (
   ni1		INT			NULL
,  ni2		INT			NULL
,  nd1		DATETIME	NULL
,  nd2		DATETIME	NULL
,  nd3		DATETIME	NULL
,  nc1		VARCHAR(9)	NULL
,  nc2		VARCHAR(9)	NULL
   )

INSERT INTO #nn (ni1, ni2, nd1, nd2, nd3, nc1, nc2)
   SELECT ai1, ai2, ad1, ad2, ad3, ac1, ac2
      FROM #a

INSERT INTO #nn (ni1, ni2, nd1, nd2, nd3, nc1, nc2)
   SELECT bi1, bi2, bd1, bd2, bd3, bc1, bc2
      FROM #b

INSERT INTO #nn (ni1, ni2, nd1, nd2, nd3, nc1, nc2)
   SELECT ci1, ci2, cd1, cd2, cd3, cc1, cc2
      FROM #c

SELECT * FROM #a
SELECT * FROM #b
SELECT * FROM #c

SELECT * FROM #ww
SELECT * FROM #nn

DROP TABLE #a
DROP TABLE #b
DROP TABLE #c

DROP TABLE #ww
DROP TABLE #nn

-PatP

jo15765 · #5 (**permalink**) 01-28-13, 22:33

Probably is just my 'newness'! I'll keep playing with it.

Looking at your post, are you saying it would be easier to just create 3 temp tables with the columns I need, then query each temp table individually to gain the result set that I am after?

Pat Phelan · #6 (**permalink**) 01-28-13, 22:43

Oh no! You definitely want just one table. The question boils down to whether it is easier for you to go with the "wide" or the "narrow" example that I posted. In most cases, the narrow answer is easier. If you have a lot of old code and especially if some of that code is "unreachable" (for example in a program that you can't modify), then the wide answer could be easier.

-PatP

blindman · #7 (**permalink**) 01-29-13, 10:00

STOP!

It does not sound to me like you need a new table. What you need is a view that combines the three datasets for reporting.

jo15765 · #8 (**permalink**) 01-29-13, 10:30

Quote:

Originally Posted by blindman

STOP!

It does not sound to me like you need a new table. What you need is a view that combines the three datasets for reporting.

Would the syntax to create a view be the same as it would be to create a table?

blindman · #9 (**permalink**) 01-29-13, 12:20

No.

Basically, you create a query that joins your tables (or UNIONs them), and then precede it with CREATE VIEW.

CREATE VIEW [YourViewName]
as
[YourSQLStatement]

All of this information is available in Books Online. This forum is an appropriate place to seek advice on best practices or architecture (Should I use a view or a table?), but basic SQL syntax questions and examples can be answered simply by referencing documentation.

Pat Phelan · #10 (**permalink**) 01-29-13, 12:27

This code actually WILL NOT WORK, but borrowing from my previous example:

Code:

CREATE VIEW vv 
   (vi1, vi2, vd1, vd2, vd3, vc1, vc2)AS
   SELECT ai1, ai2, ad1, ad2, ad3, ac1, ac2
      FROM #a
   UNION ALL
   SELECT bi1, bi2, bd1, bd2, bd3, bc1, bc2
      FROM #b
   UNION ALL
   SELECT ci1, ci2, cd1, cd2, cd3, cc1, cc2
      FROM #c

The reason that this view won't work is because you can not create a view on a temporary table... If these were normal tables (no #), then this syntax should work nicely.

-PatP