Post reply

DDQ for an Insert and Update

  • January 5, 2007 at 8:40 am

    #171736

    Hi.  I've been searching the forums on this site and many others for weeks as well as trying to use a reference book for guidance, but I am still having trouble with this.  I am pretty new to using DTS.  I've used the Import/Export wizard a lot, but have never had to create packages.  Now, I am in a do or die situation.  If anyone can help, I would really appreciate it.

    Problem:  Data located in a SQL Server database needs to be inserted if new or updated if changed in a MySQL database.  We are running SQL Server 2000 and MySQL 3.5.  For a connection, I am using the MySQL 3.51 ODBC.  The connection works.  I am able to bulk insert without a problem.  Unfortunately, I do not know VB very well.  I know I need to develop an ActiveX script.  But I'm not sure where to start.

    What I've done so far:  First, I set up 2 global variables.  One receives all VersionID's from the SQL Server table.  The second receives all VersionID's from the MySQL table.  Now I stall.  How do I compare the record sets to determine whether I need to insert a new row or I need to update a record?  I created a DDQ task.  I chose my SQL Server table, Version_Master as my source.  My MySQL table, Version_Master, is the bindings table.  In transformations, I have deleted the mappings, clicked New, selected ActiveX, and clicked Properties.  At this point, I've tried to play around but I really am not getting any where.  If anyone has some time to assist me, I would be forever indebted. 

    Cathy D.

  • January 5, 2007 at 10:56 am

    #681534

    Cathy,

    I generally stay away from the Data Driven Query task because it's performance is so poor. 

    I prefer to import data into staging tables then use TSQL in Execute SQL tasks to update and insert from the permanent tables.

    The insert task uses this syntax:

    INSERT INTO .... WHERE NOT EXISTS

    and the update task uses this:

    UPDATE SET .... WHERE PERMKEY = STAGINGKEY AND

    (PermCol1 <> StagingCol1 Or PermCol2 <> StagingCol2 ... etc)

    Greg

     

    Greg

  • January 5, 2007 at 11:12 am

    #681539

    Thanks Greg.  I did consider staging tables, but that would require a lot of tables.  I wasn't sure I wanted to create so many.  I'll definitely think more on it.  It's a fall back for me at any rate

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply