Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.