2 Replies Latest reply on Nov 3, 2010 3:08 PM by Dan Bracuk

    Trying to write an SQL import script


      Hello All,


      I am trying to write a bulk importer for data for my clients and I am just about there. However I am running into a problem updating records in a second table.


      I want to do all this through the SQL Server for resource purposes.


      The SQL statement will go through the entire temp table (users to be added or updated) and compare it to the main existing table. First it will compare records and determine who are exactly the same (no changes) and then delete those records out of the temp table. Now it will find the records that are in the table (by employee ID), but have changes in their profiles (name, number, etc.).


      This is my problem:


      When I compare the phonenumbers in a separate table, which are connected by a sub_id. Example


      Main Table: (Fields) sub_id, fname, lname, address, groups, subgroups, etc.

      Phonenumbers Table: sub_id, account_id, sub_user_number, active


      Right now, I am performing update statements:


      -- Update sub_user_number
      print('Update sub_user_number');
      print('  -sub_user_number3');
      SET sub_user_number = i.sub_user_number3
         ON i.misc1 = s.misc1
         ON g.sub_id = s.sub_id
        WHERE s.account_id = @account_id
        AND isnull(i.sub_user_number, '') <> isnull(s.sub_user_number, '')
        AND g.group_id IN (@group_id)


      The problem is that if I have three numbers, it updates all three numbers with the same number.


      Ideally what I would like to do is completely delete the numbers and then re-enter them. For example:


      Perform a DELETE of all numbers out of PHONENUMBERS table where maintable.sub_id = temptable.sub_id


      LOOP through numbers found in temptable

      INSERT new number into PHONENUMBERS (sub_id, account_id, active, sub_user_number)

      END LOOP


      Therefore at the end of the loop, we will have entered three numbers (two that were originally in there and one that is new)


      Could someone help me with the SQL syntax that will work in SQL Server Query analyzer?



      Thank you VERY much for anything you all can do.

        • 1. Re: Trying to write an SQL import script
          -==cfSearching==- Level 4

          LOOP through numbers found in temptable


          I have only skimmed this thread, but I would say do not loop unless it is truly needed.  Databases are designed to work best with "sets" of information. So it is usually more efficient to process a group of records than one record at a time.


          What I usually do with imports is run a series of bulk updates on my #temp table(s) to identify which items are "new" which ones are "changed". When finished I can easily JOIN to the base tables and do a single UPDATE for changed records and INSERT for new records.  Usually a lot more efficient than looping one record at a time.



          I want to do all this through the SQL Server for resource



          BTW: Not to ignore your question, but database questions are usually best answered in a database forum (in your case MS SQL).  Because once you get the information into the #temp table, it is strictly sql from there and any database guru could assist. They do not have to know anything about CF ;-)


          Message was edited by: -==cfSearching==-

          • 2. Re: Trying to write an SQL import script
            Dan Bracuk Level 5

            What's the primary key of your phone number table?