locked
Copying Table Data from One Table to Another RRS feed

  • Question

  • Hello All;

    I want to copy Header Data from one table to another. I know how to do this if the table data is exactly the same, but I need to generate a new HeaderKey for the destination table.

    Example - I am copying SalesOrder Header Data into the PickList Header Table, but I need to also add a PickListHeaderKey when I do so. It cannot be the SalesOrderHeaderKey which will be copied also.

    Source Table (Sales Order)                  DestinationTable (PickListHeaderTable)

    SalesOrderHeaderKey                          SalesOrderHeaderKey

    CustomerID                                        CustomerID

    DivisionID                                           DivisionID

    SalesOrderTotal                                  SalesOrderTotal

    etc.                                                    etc

    (no column)                                        PickListHeaderKey

    PicklistHeaderKey has to be the PrimaryKey to the table, which means I have to generate this first and insert into table.

    cmd =

    New SqlCommand("Insert Into PickListHeaderTable SELECT * FROM SalesOrderHeaderTable WHERE SOStatus = @SOStatus ", con)

    cmd.Parameters.Add(

    "@SOStatus", SqlDbType.VarChar).Value = "COMMITTED"

    How can I edit this command to include the PickListHeaderKey, which needs to be inserted at the same time or before the rest of the data is copied?

    Thank you very much.

     

    Sunday, August 15, 2010 6:36 PM

Answers

  • Hi Tlerew325,

    If you have set the PickListHeaderKey column as the identity column, when you insert the data from Sales Order table into PickListHeaderTable, the values for PickListHeaderKey will be auto-generated. So you do not need to generate this first then insert data into table. And you will not need to edit that command.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Liang Monday, September 13, 2010 8:33 AM
    Thursday, August 19, 2010 9:49 AM

All replies

  • Probably only with a loop.

    A datatable is a little bit strange class that it has its column describtion in another property than its rows (items)

    Be aware that if you need to things like you describe you have probably a bad normalized database.

    http://en.wikipedia.org/wiki/Database_normalization


    Success
    Cor
    Sunday, August 15, 2010 6:58 PM
  • The whole idea about the Primary Key is because a Sales Order can have mutiple Pick Lists, but a Pick List can only have one Sales Order. Therefore, the SalesOrderKey is the Primary Key in the SalesOrderTable but cannot be in the PickListHeaderTable, but will be linked by it (Foreign Key).

    The tables are constructed correctly, but I need to know how, if possible, to bury a nested query into my existing query to write a new PickListHeaderKey to the destination table and then insert(update) the rest of the data.

     

    Sunday, August 15, 2010 7:09 PM
  • Hi Tlerew325,

    If you have set the PickListHeaderKey column as the identity column, when you insert the data from Sales Order table into PickListHeaderTable, the values for PickListHeaderKey will be auto-generated. So you do not need to generate this first then insert data into table. And you will not need to edit that command.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Liang Monday, September 13, 2010 8:33 AM
    Thursday, August 19, 2010 9:49 AM