Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Question Unanswered: Create New Table From Several Existing Tables?

    I have three tables :

    England_Summer_2001
    England_Summer_2002
    England_Summer_2003

    The tables have the following columns :

    Player, Position, [From], [To], Fee, Type, ID, League, Window

    I want to create a new table, EnglandFinal with all the data from the three tables although I'm guessing it would not be a good idea to copy the primary keys (ID column) as they would clash.

    I have played around with CREATE and INSERT into and UNION but I get various errors. I'm sure I've done this before!

    This creates a table from a single table :

    select * into Final
    from England_Summer_2001
    Last edited by Palermo; 04-08-12 at 12:59.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If the id column is the primary key and it's not unique across the tables, you can do something like this:
    Code:
    SELECT row_number() over (order by old_id) as id,
           player,
           position, 
           league_year
           ...
      INTO final
    FROM ( 
        SELECT id as old_id, player, position, 2001 as league_year, ...
        FROM england_summer_2001
        UNION ALL
        SELECT id as old_id, player, position, 2002 as league_year, ...
        FROM england_summer_2002
        ...
    ) t
    Alternatively you could keep the "old" ids and define the primary key as (id, league_year) instead.

    Btw: this is a very good example on why such a table structure is a bad idea. You should have used a single table right from the start.

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    England_Summer_2001
    England_Summer_2002
    England_Summer_2003
    Looks alike a bad data model to me.

    You also have
    England_Winter_2001
    England_Spring_2001
    England_Autumn_2001
    tables?

    And how about
    Wales_Summer_2002
    Scotland_Winter_2001
    ....
    ?

    My gut feeling tells me your data model is flawed.

    This is one possible way to do what you asked. It won't solve all the data model flaws that I think are present in the current model.

    Code:
    CREATE TABLE Final(
    	Id		INT	IDENTITY(1, 1)	NOT NULL,
    	SoccerYear	INT	NOT NULL,
    	Season		CHAR(2)	NOT NULL	
    		CONSTRAINT CC_Final_Season CHECK (Season IN ('WI', 'SP', 'SU', 'AU')),
    	Location	VARCHAR(20)	NOT NULL,
    	PlayerId	INT, 
    	Position	VARCHAR(20), 
    	[From]		DATE, 
    	[To]		DATE, 
    	Fee		DEC(15, 2),	
    	TypeId		INT, 
    	OldID		INT	, 
    	LeagueId	INT	, 
    	Window		VARCHAR(20),
    	CONSTRAINT PK_Final PRIMARY KEY (Id)
    )
    
    INSERT INTO FINAL(SoccerYear, Season, Location, PlayerId, Position, [From], [To], Fee, TypeId, OldID, LeagueId, Window)
    select 
    	2001,
    	'SU',
    	'England',
    	Player, 
    	Position, 
    	[From], 
    	[To], 
    	Fee, 
    	Type, 
    	ID, 
    	League, 
    	Window
    from England_Summer_2001 
    
    ....
    
    INSERT INTO FINAL(SoccerYear, Season, Location, PlayerId, Position, [From], [To], Fee, TypeId, OldID, LeagueId, Window)
    select 
    	2002,
    	'WI',
    	'Scotland',
    	Player, 
    	Position, 
    	[From], 
    	[To], 
    	Fee, 
    	Type, 
    	ID, 
    	League, 
    	Window
    from Scotland_Winter_2002 
    
    ....
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by shammat View Post
    If the id column is the primary key and it's not unique across the tables, you can do something like this:
    Code:
    SELECT row_number() over (order by old_id) as id,
           player,
           position, 
           league_year
           ...
      INTO final
    FROM ( 
        SELECT id as old_id, player, position, 2001 as league_year, ...
        FROM england_summer_2001
        UNION ALL
        SELECT id as old_id, player, position, 2002 as league_year, ...
        FROM england_summer_2002
        ...
    ) t
    Alternatively you could keep the "old" ids and define the primary key as (id, league_year) instead.
    Each table as ID as a primary key. This could easily be created afterwards. How would I go about creating a new table without copying the IDs?

    Btw: this is a very good example on why such a table structure is a bad idea. You should have used a single table right from the start.
    The data is imported from over 200 Excel worksheets so I figured it would be easier to merge them together in SQL than in Excel.
    Last edited by Palermo; 04-08-12 at 15:35.

  5. #5
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by Wim View Post
    England_Summer_2001
    England_Summer_2002
    England_Summer_2003
    Looks alike a bad data model to me.

    You also have
    England_Winter_2001
    England_Spring_2001
    England_Autumn_2001
    tables?
    No. These cover football transfer windows so :

    England_Summer_2001
    England_Winter_2001

    All the way upto 2011 for seven leagues in total.

    I want to merge all the summer tables into one table (70 tables) and all the winter tables into one table (another 70 tables). The reason for this is so I don't have to list every single table when using a UNION query.

    Btw: this is a very good example on why such a table structure is a bad idea. You should have used a single table right from the start.
    I know but the data is from Excel worksheets.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Palermo View Post
    Each table as ID as a primary key. This could easily be created afterwards. How would I go about creating a new table without copying the IDs?
    My statement is not copying the original ids, it's simply creating new ones while copying all other columns

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I want to merge all the summer tables into one table (70 tables) and all the winter tables into one table (another 70 tables).
    Put those 140 Excel worksheets into one table, as I showed you. Use the Season column (or any better name you come up with) to store the Winter/summer difference.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by shammat View Post
    My statement is not copying the original ids, it's simply creating new ones while copying all other columns
    There's no field League_year and

    Code:
    Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'FROM'.
    Msg 156, Level 15, State 1, Line 11
    Incorrect syntax near the keyword 'FROM'.
    Please show an example that adds just two columns player and position. Once I know the correct syntax I can add the other fields.

  9. #9
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by Wim View Post
    Put those 140 Excel worksheets into one table, as I showed you. Use the Season column (or any better name you come up with) to store the Winter/summer difference.
    Because putting all those worksheets into one table will be quicker than merging them in SQL?!?!?!? The point is to save time not spend more time.

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you tell me how putting all those worksheets in 1 table would take longer compared to putting them in 2 different tables?

    The point is I'm trying to get you a better data model.

    Better data model = SQL to process data is easier to write and faster for RDMS to process
    Last edited by Wim; 04-08-12 at 19:30.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by Wim View Post
    Can you tell me how putting all those worksheets in 1 table would take longer compared to putting them in 2 different tables?
    20 Excel Tables
    7 worksheets in each table

    I wanted to avoid having to compile the data in Excel again as it's already in the database but what you say makes sense so I'll start again.
    Last edited by Palermo; 04-08-12 at 19:39.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Palermo View Post
    There's no field League_year
    That is being created on the fly through this expression 2001 as league_year so that you can distinguish where each row came from in the final table.

    Please show an example that adds just two columns player and position. Once I know the correct syntax I can add the other fields.
    I don't understand you. I gave you that example (obviously you have to remove the ... from my example)

  13. #13
    Join Date
    Dec 2010
    Posts
    36
    Quote Originally Posted by shammat View Post
    That is being created on the fly through this expression 2001 as league_year so that you can distinguish where each row came from in the final table.

    I don't understand you. I gave you that example (obviously you have to remove the ... from my example)
    This is the query :

    Code:
    SELECT row_number() over (order by old_id) as id,
           player,
           position, 
           league_year
      INTO final
    FROM ( 
        SELECT id as old_id, player, position, 2001 as league_year,
        FROM england_summer_2001
        UNION ALL
        SELECT id as old_id, player, position, 2002 as league_year,
        FROM england_summer_2002
    ) t
    Code:
    Msg 156, Level 15, State 1, Line 8
    Incorrect syntax near the keyword 'FROM'.
    Msg 156, Level 15, State 1, Line 11
    Incorrect syntax near the keyword 'FROM'.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    There is a trailing comma after league_year which is not necessary as no more columns are selected.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you formatted this way(i.e. put a comma left to next element(columns, tables, so on...)),
    it would be easier to find such syntax violations.

    Code:
    SELECT row_number() over (order by old_id) as id
         , player
         , position
         , league_year
     INTO  final
     FROM (
           SELECT id as old_id
                , player
                , position
                , 2001 as league_year
            FROM  england_summer_2001
           UNION ALL
           SELECT id as old_id
                , player
                , position
                , 2002 as league_year
            FROM  england_summer_2002
          ) t

Tags for this Thread

Posting Permissions

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