locked
Copying Data from One table to another table RRS feed

  • Question

  • Hello,

    I have one table with data in it. However I need to add a column to the table. I also need to add the column before two existing columns at the end of the table. I have created a new table with a diffrent name. I have then inserted the data into the new table with the new column by:

    Insert into Table B

    Select * from A

    However I think that because I had rearrange the columns/altered the ordering of the columns. The columns that i rearranged did not have the correct data into for some reason. Any ideas?


    bryan

    Wednesday, April 10, 2013 1:31 PM

Answers

  • Also, why do you care about the column order?

    Hopefully you're not using "SELECT *" (right?!), so it shouldn't really matter.

    Don't use SELECT * 

    explicitly define the Columns in INSERT and SELECT statements.

    Bellow are steps:

    1) Create your Destination Table:

    2) INSERT INTO DestinationTABLE (ColA,ColB, ColC)

    SELECT Col1, Col2, Col3 

    FROM SourceTable;


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Thursday, April 11, 2013 7:29 AM
  • The INSERT INTO statement has an optional column list. For example "INSERT INTO B (col1, col2, col3)". If you omit this column list (as you have done), the engine will assume all columns in the order in which the columns were once declared in this table "B"

    If you use "SELECT * FROM A", the engine will select all columns in the order in which the columns were once declared in this table "A".

    For production code, it is a bad practice to omit the Insert column list. It is also a bad practice to use Select *.

    If you specify both, and you make sure the ordinal positions match (i.e. same order of columns), then you should be just fine.


    Gert-Jan

    Thursday, April 11, 2013 5:32 PM

All replies

  • if you want to add a column before your two existing columns, you need to recreate your table( drop and create your table ), you cannot alter your table and rearrange the order of your columns. After you create your table, then you can insert the data like you want ( SELECT * FROM INTO... , INSERT INTO tableB SELECT * FROM tableA.

    Be sure the columns data type are the same in both before you perform the insert.


    Sergio Sánchez Arias
    AYÚDANOS A AYUDARTE

    Wednesday, April 10, 2013 1:36 PM
  • You need to align the column list from your source table with your target table. 

    Insert into TableB (col1,col4,col3)

    Select col1, col4, col3 from A

    Wednesday, April 10, 2013 2:32 PM
  • Also, why do you care about the column order?

    Hopefully you're not using "SELECT *" (right?!), so it shouldn't really matter.

    Wednesday, April 10, 2013 7:15 PM
  • Also, why do you care about the column order?

    Hopefully you're not using "SELECT *" (right?!), so it shouldn't really matter.

    Don't use SELECT * 

    explicitly define the Columns in INSERT and SELECT statements.

    Bellow are steps:

    1) Create your Destination Table:

    2) INSERT INTO DestinationTABLE (ColA,ColB, ColC)

    SELECT Col1, Col2, Col3 

    FROM SourceTable;


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Thursday, April 11, 2013 7:29 AM
  • The INSERT INTO statement has an optional column list. For example "INSERT INTO B (col1, col2, col3)". If you omit this column list (as you have done), the engine will assume all columns in the order in which the columns were once declared in this table "B"

    If you use "SELECT * FROM A", the engine will select all columns in the order in which the columns were once declared in this table "A".

    For production code, it is a bad practice to omit the Insert column list. It is also a bad practice to use Select *.

    If you specify both, and you make sure the ordinal positions match (i.e. same order of columns), then you should be just fine.


    Gert-Jan

    Thursday, April 11, 2013 5:32 PM