If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Creating T-SQL Table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
Creating T-SQL Table

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.
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,808
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
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).
Reply With Quote
  #4 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,808
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
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?
Reply With Quote
  #6 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,808
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #7 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
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.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
Quote:
Originally Posted by blindman View Post
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?
Reply With Quote
  #9 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
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.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #10 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,808
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
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 123
Thank the both of you for the insight. I will def create a view, and it shouldn't take me to long to get the syntax correct
Reply With Quote
  #12 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Go ahead and post it when you are done, and we'd be happy to give you tips to improve its clarity or performance.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On