SQL query needed

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

  • : 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.

Howdy, Stranger!

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

Categories

In this Discussion