Copying data from one table to another RRS feed

  • Question

  • table A has columns A,B,X,Y,Z

    table B has columns A,B,C,D,E,F,G,H

    I am trying to copy data from table A to table B (i.e columns A and B should be copied). To do this I could use

    Insert into TableB (A,B) select A,B from TableA


    Is there anyway to do this without hardcoding column names. Ie will the statement below work?

    Insert * into TableB  from TableA?






    Tuesday, November 1, 2011 2:38 PM


  • Hi chrismortonjones,

    No, in this scenario you really have to list the column names.

    If both tables had the exact same amount of columns, all corresponding
    in type, and all in the same order, you could use

    SELECT * FROM TableA;

    But in that case, the correctness of your code would be very fragile -
    anyone modifying something to any table could break it. And also, the
    above code is only about number and position of columns; no attempt is
    made to match column names.

    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Tuesday, November 1, 2011 3:27 PM