Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    130

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

  3. #3
    Join Date
    Feb 2012
    Posts
    130
    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).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

  5. #5
    Join Date
    Feb 2012
    Posts
    130
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

  8. #8
    Join Date
    Feb 2012
    Posts
    130
    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?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,924
    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.

  11. #11
    Join Date
    Feb 2012
    Posts
    130
    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

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •