Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Location
    Texas
    Posts
    3

    Unhappy Unanswered: INSERT INTO SELECT to table with more columns

    This one might be simple one but I can not find a fix anywhere on the web. I have two tables...TableA with 65 columns and TableB with only 60 columns. The two tables are the same as for column headers except for the additional 5 columns in TableA. I was trying:

    INSERT INTO TableA SELECT * FROM TableB WHERE Item = 'Something'

    but I error out because of the additional 5 columns. I have NO control of the design of the tables.

    Anyone have any Ideas?
    Thanks!!
    PS. I also tried:
    INSERT INTO (TableA.Column1,TableA.Column2,....) Value (TableB.Column1, TableB.Column2,...)
    but it would not let me being that was too may lines.
    Last edited by cornfed; 08-19-09 at 03:22.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    INSERT INTO TableA (col1, col2...)
    SELECT col1, col2... 
    FROM TableB 
    WHERE Item = 'Something'
    Avoid using select * for your source, and not defining the columns in your destination table - the code will break if either tables' schema changes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Location
    Texas
    Posts
    3
    Being that both tables have 60+ columns I was hoping to not have to go that route. Thats right....I am lazy! I will give it a try.

    Thanks!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can write a little script utility to produce your column names using the system tables. Or you can try using something like SQL Prompt that will write that stuff out for you (as well as other useful time savers).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2009
    Location
    Texas
    Posts
    3

    Smile

    I never thought of that. That sounds great.

    Thanks for all of the help.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's one method that I use:
    Code:
    DECLARE @columns varchar(max)
    
    SELECT @columns = Coalesce(@columns + ', ', '') +  column_name
    FROM   information_schema.columns
    WHERE  table_schema = 'dbo'
    AND    table_name = 'people'
    ORDER
        BY ordinal_position
    
    PRINT @columns
    
    SET @columns = NULL
    
    SELECT @columns = Coalesce(@columns + ', ', '') +  name
    FROM   sys.columns
    WHERE  object_id = Object_ID('dbo.people')
    ORDER
        BY column_id
    
    PRINT @columns
    George
    Home | Blog

Posting Permissions

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