Trying to write an SQL import script

[Guest]

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');
UPDATE WENS..SUBSCRIPTION
SET sub_user_number = i.sub_user_number3
  FROM WENS_IMPORT..IMPORT_INSPIRON i INNER JOIN WENS..SUBSCRIPTION s
   ON i.misc1 = s.misc1
  LEFT JOIN WENS..ACCOUNT_USER_GROUPS g
   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.

[-__cfSearching__-]

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

purposes.

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==-

[Dan_Bracuk]

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