none
Update Sql table from another Sql table

    Question

  • Hello, I am very new to programming, so sorry for asking what may seem like simple questions.

    I have 2 SQL tables. The first one contains data that is gathered through an automated process. It looks like this.

    The Destination table looks like this.

     

    I am looking for ideas on how to do this. I would prefer to match it up by serial number, but it really needs to give options or multiple choices. The computer names in the destination are mostly wrong and the serial numbers may or may not be complete. There are also duplicates in the destination database.

     

    I have another table that list Store ID's and IP addresses at each store. I would like to update the Store ID in the destination based on that data as well.

     

    If anyone has any idea on this, I would love to hear them.

     

    Thank you.

    Sunday, October 23, 2011 6:18 AM

Answers

  • Hello dc Dudley,

    I don't see any related key in your two tables, that makes an automated process very difficult. 

    You really should have an related identifier to make an automatic process possible. 

    If you have one then it is quite simple. 

    Create two (new) datatables

    Fill the first with the data from the first database table with the identifier as where clause in your select statement

    Fill the second with the data from the second database table with the same identifier (if it not exist it creates an empty datatable)

    If there is no row in the second one, than add a new row.

    Set the properties of that second datatable. With a simple loop and investigating the columnname in the first one.

    Update the second datatable.

     


    Success
    Cor
    Tuesday, October 25, 2011 6:00 AM

All replies

  • In the way I read it, are you creating redundancy, one of the first thing you have to learn using databases. 

    Look at this page on Wikipedia about normalization.

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

    In .Net programming you set in the SQL Server a relation and use all the time both tables to get the data.

    So there is nothing to set, organize your server well and always use the data.

    ADONet (certainly in Framework 4) has more than enough possibilities to do that.

     


    Success
    Cor
    Monday, October 24, 2011 6:37 AM
  • Hi Bcdudley,

    Welcome to the MSDN Forum.

    Please try this kind of sql statement:

    Insert into DestTable (column1, column3, column4,column6,column9)

    Select * from sourceTable

    I hope this will be helpful.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 24, 2011 10:11 AM
    Moderator
  • Thank you for the responses. I am not really creating redundant databases if that is what you are asking. The source database has data inserted from live computers that are up and running on our network. It also includes a very large amount of other useless data (useless to me). Once a complete audit is done on all the computers on our network, the source database contains 20 - 50 million rows and takes a very long time to read any data out of it. I would like to just extract the data I need and move it to a more usable format in the destination database.

     

    The destination database also contains information about computers that are offline or still in inventory waiting to be shipped out to our users.

     

    I will try the Insert and Select statements, I was more so looking for a method to match up the source and destination information. I do not want to completely replace the data in the destination, just update it selectively.

     

    Thank you both for helping out a very green programmer trying to write a program way above my head.

    Monday, October 24, 2011 9:53 PM
  • Hi Bcdudley,

    >>I do not want to completely replace the data in the destination, just update it selectively.

    If so, you could to do it by two steps:

    1. insert all data which is not exist in the destination table:

    Insert into DestTable (column1, column3, column4,column6,column9)
    Select * from sourceTable
    where soureTable.PrimarykeycolumnName not in (select distinct DestTable.PrimaryKeycolumnName from DestTable)
    

    2. Update the the same primary key column.

    Here you need a sql procedure, to select the data which need to be updated from the destination table and then update it with the source table data.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, October 25, 2011 5:45 AM
    Moderator
  • Hello dc Dudley,

    I don't see any related key in your two tables, that makes an automated process very difficult. 

    You really should have an related identifier to make an automatic process possible. 

    If you have one then it is quite simple. 

    Create two (new) datatables

    Fill the first with the data from the first database table with the identifier as where clause in your select statement

    Fill the second with the data from the second database table with the same identifier (if it not exist it creates an empty datatable)

    If there is no row in the second one, than add a new row.

    Set the properties of that second datatable. With a simple loop and investigating the columnname in the first one.

    Update the second datatable.

     


    Success
    Cor
    Tuesday, October 25, 2011 6:00 AM