SQL query needed - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL query needed

hesselbacherhesselbacher Posts: 1Member
I have written a script to search for a gather information on every PC in active directory. I used bcp to put this information to an SQL database in sql server 7.0.
What I need is a query that will take that data from one table and insert it or update it depending if it exist on the second table. I would like to create a stored procedure or a trigger that will do this. It sounds easy enough, but for some reason I just can't get it to work. Any help or example you can give would be appreciated.


Thank you
James H

Comments

  • paulj59paulj59 Posts: 420Member
    : I have written a script to search for a gather information on every PC in active directory. I used bcp to put this information to an SQL database in sql server 7.0.
    : What I need is a query that will take that data from one table and insert it or update it depending if it exist on the second table. I would like to create a stored procedure or a trigger that will do this. It sounds easy enough, but for some reason I just can't get it to work. Any help or example you can give would be appreciated.
    :
    :
    : Thank you
    : James H
    :
    :

    I always insert 'placeholders' for the new records, then update all. My tables always have defaults. Sometimes there is a problem with foreign keys so I create a record with ID=0 in the foreign table so that the insert won't fail.

    insert into #Recs(ID)
    select table1_id
    from Table1
    where isTransferred=0 ' or however you flag them

    delete #Recs from #Recs inner join table2 on #Recs.ID=table2.table2_ID

    insert table2(ID)
    select ID
    from #Recs

    'you can also do the above with a NOT IN, but NOT IN is really slow

    'if your defaults can't handle your foreign keys then you have to fudge it
    insert table2(ID, other1_id, other2_id)
    select ID, acceptable1_id, acceptable2_id
    from #Recs

    'then update all of them
    update table2
    set table2.field1=table1.field1
    from table2 inner join table1 on table2.table2_id=table1.table1_id
    where table1.isTransferred=0 ' or however you flag them

Sign In or Register to comment.