Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    9

    Unanswered: Performing Data Updates

    Is there an easy way of selecting data out of a source table and inserting the information I need into my main Items table and then at the same time updating my source table with the id number of the row that was created? does that make sense?

    my structure looks like
    destination:
    CREATE TABLE [Items] (
    [id] [int] NOT NULL identity PK,
    [typeid] [int] NOT NULL ,
    [description] [varchar] (200))
    GO

    Source:
    CREATE TABLE [ItemInfo] (
    [ItemID] [int] NOT NULL FK,
    [source_ID] [int] NULL ,
    [source_price] [money] ,
    [source_status] char(1)
    [source_url] varchar(100))
    GO

    I need to import the data I need from the source > dest then update the source with the itemID and also allow for weekely add/change/delete updates to the source data, which when run will modify the corresponding itemID in the Items table. what is the simplest way to do this in sql server - assume create a DTS but keeping the integrity between the two tables on the itemID is whats confusing me.

    thanks!

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    How do you know when you have an update or delete? May be you could post more details of your process.

    Aside from the above I would suggest loading your data into an table, inserting new recordes into the ITEMS table, then select data from the ITEMS table joined to the import table so you can pickup the newly creted identity and insert the new identity and ItemInfo into the ITEMINFO table.

    For an update you skip the first step above and do an update on the ITEMINFO table by joining the ITEMS table and your import table.

    Again post a few more details and I or someone can probably helpout.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    May 2002
    Posts
    18

    Re: Performing Data Updates

    I really dont understand anything of the actual problem. Could you make things clear by quoting with some example input data and output data ?



    Originally posted by cDc
    Is there an easy way of selecting data out of a source table and inserting the information I need into my main Items table and then at the same time updating my source table with the id number of the row that was created? does that make sense?

    my structure looks like
    destination:
    CREATE TABLE [Items] (
    [id] [int] NOT NULL identity PK,
    [typeid] [int] NOT NULL ,
    [description] [varchar] (200))
    GO

    Source:
    CREATE TABLE [ItemInfo] (
    [ItemID] [int] NOT NULL FK,
    [source_ID] [int] NULL ,
    [source_price] [money] ,
    [source_status] char(1)
    [source_url] varchar(100))
    GO

    I need to import the data I need from the source > dest then update the source with the itemID and also allow for weekely add/change/delete updates to the source data, which when run will modify the corresponding itemID in the Items table. what is the simplest way to do this in sql server - assume create a DTS but keeping the integrity between the two tables on the itemID is whats confusing me.

    thanks!

  4. #4
    Join Date
    Nov 2002
    Posts
    9
    My ItemInfo table has already been populated from a CSV file in this case.

    I need to populate the description and type fields in my Items table (which is currently empty) with data from itemInfo, and at the same time update the ItemInfo FK with the identity key in the Items table.

    The problem I have is, I want my identity PK in the Items tables to be my only key to the ItemInfo table - and my ItemInfo table does not have any data in that field thats what I need to populate aswell.

    I can add an additional field into Items to hold the source_id of the row that gets added and then update ItemInfo, this doesn't seem like a very good way of maintaining the relationship.

    Changes to ItemInfo are via weekly CSV file updates and to update my ItemSource I currently import my update CSV into a temporary table (which has an ACD flag) and run the Add/change/deletes from this into ItemInfo, I need to extend this though so that when an ItemInfo row is changed or deleted the corresponding key in the Items table maintained.

  5. #5
    Join Date
    May 2002
    Posts
    18
    Problem partially understood. Why dont u make things clear by providing a sample input and output as I asked you earlier ? One thing that is not clear is the criteria to establish a relationship between item and iteminfo. For example assume the following data


    ItemInfo Table
    ItemID SourceID Source_Price Source_Status Source_URL
    1 12 y test1
    2 13 y test2
    3 14 n test3


    Item Table is empty.
    Also ItemID column data is empty (as you stated).


    Now clarify how you want the Item table to be populated by providing a sample output.



    Originally posted by cDc
    My ItemInfo table has already been populated from a CSV file in this case.

    I need to populate the description and type fields in my Items table (which is currently empty) with data from itemInfo, and at the same time update the ItemInfo FK with the identity key in the Items table.

    The problem I have is, I want my identity PK in the Items tables to be my only key to the ItemInfo table - and my ItemInfo table does not have any data in that field thats what I need to populate aswell.

    I can add an additional field into Items to hold the source_id of the row that gets added and then update ItemInfo, this doesn't seem like a very good way of maintaining the relationship.

    Changes to ItemInfo are via weekly CSV file updates and to update my ItemSource I currently import my update CSV into a temporary table (which has an ACD flag) and run the Add/change/deletes from this into ItemInfo, I need to extend this though so that when an ItemInfo row is changed or deleted the corresponding key in the Items table maintained.

  6. #6
    Join Date
    Nov 2002
    Posts
    9
    The items table should be as follows

    ItemInfo Table
    ItemID SourceID Source_Price Source_Status Source_URL
    1 12 y test1
    2 13 y test2
    3 14 n test3

    Items
    id typeid description
    1 1 test1
    2 1 test2
    3 1 test3

    thanks

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Can you post the ddl from the temporary table? Also where does Items.typeid come from?

    So let me repeate and see if I understand...

    Data is loaded into a temp table, the data has an indicator (A/C/D) to let you know if this is New, Changed or Obsolete data.

    At present, you have some code you run to move data from your temp table to the "ItemInfo" table. You have yet to populate the "Items" table.

    Let me know if I am on target and hopefully include the ddl for the temp table.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Nov 2002
    Posts
    9
    ignoring the data update for the time being, I just need to populate Items from Iteminfo, but at the same time create a relationship between the two on the ItemID field, which is currently empty.

  9. #9
    Join Date
    Nov 2002
    Posts
    9
    I managed to acheive this using a cursor and updating my foreign key with @@identity as I inserted the data, I am not sure if this is the best way but it worked.

Posting Permissions

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