Iterate thru one table add records in another table if they don't already exist.

Hi Experts,
I need to update all the accounts based on Global Account. I am doing it in a stored procedure(SQL server 2005). Using Old way, is to create outer cursor for all the fields in Global account. Then create second cursor within this outer cursor for each account. if that record does not exist, add it and move to the next record from the global account.
I  have heard that one should not use Cursors any more for performance issues. How would I accomplish it the modern, more efficient way?
My pseudo code would look like:
Select * from GlobalAccounts
Select * All_Accounts
 While not EOF.All Accounts
         GlobalAccount.MoveFirst
               While not GlobalAccount.EOF
                    if GlobalAccount.record is not already present in AllAccount.current record then,
                           AllAccount.CurrentRecord  Add GlobalAccount.Current Record
                   end if
                   GlobalAccount.MoveNext
            Wend
          All Accounts.MoveNext
 wend
The only way i know is to use cursor. Please help me if there is a better way to do it so performance is not slow. Thank you
RekhaShahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gladfellowCommented:
Let me state my understanding of the problem:

You need to insert into All_Accounts those rows from GlobalAccounts, which are not present in All_Accounts. If this is the correct understanding, then the SQL is straightforward.

insert into All_Accounts (column name 1, column name 2, ...)
select  (GA.column value 1, GA.column value 2, ...) from GlobalAccounts GA
where not exists (select * from All_Accounts AA where GA.key = AA.key)

The above type of query is known as a correlated sub-query. You need to have a key in both the tables for easy comparison of existence; otherwise you may end up comparing all columns in the where clause. GA and AA are aliases for shortcut reference of the tables in the rest of the query.

Hope this helps.

- Venkat
0
Patrick MatthewsCommented:
No need for cursors or subqueries for this one, as a simple insert will do.

Assuming the two tables can be joined on an ID column:


INSERT INTO All_Accounts (ID, col1, col2, col3)
SELECT g.IF, g.col1, g.col2, g.col3
FROM GlobalAccounts g LEFT JOIN
    All_Accounts a ON g.ID = a.ID
WHERE a.ID IS NULL

Open in new window

0
gladfellowCommented:
Sure ... Join seems a good and attractive alternative...

Leaving the query performance acceptability factor aside (as it depends on a particular version of the engine and also the actual data volume) ...

I have so far preferred the sub-query over join, as the former is more a statement of requirement than the latter, which is a (translated) design statement of the problem.

All said, if one is used to JOINS then Matthew's suggestion (JOIN alternative) definitely is a more attractive option.

- Venkat
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Patrick MatthewsCommented:
Hm, seems I did a poor job of reading Venkat's post before writing my own.  Using EXISTS is a fine way to do it.

I'd be curious to see whether there is any meaningful difference in run time for the two approaches.
0
RekhaShahAuthor Commented:
i guess, i did not explain it right.  Let me see, If I can explain it better.  I am creating profile for many customers. So I have a Profile table with different Accounts. Each account can have variable number of records in this table. Also I do not have a primary key in this table. Do I need one? it is just two fields table.
 So, if i select "SELECT * FROM PROFILE WHERE ACCTID = 1", I will get x number of records. If i do the same query for ACCTId =2, I will get y number of records.
If i select  "SELECT * FROM PROFILE WHERE ACCTID = Global_ID", I will get z number of records. I want to take these z number of records, check it for each accountId in this table, if these records are not present for each of the account, I want to add that record for that Account Id.
e.g.
AcctId        Property
    1                AA
    1                BB
    1                CC
    2                AA
    2                xx
    3                AA
    3                XX
   100               AA
  100           BB
  100               CC
  100              DD
  100             XX
  100              YY
  100            ZZ
  100         NN
if 100 is my Global AccountId, I want to get all the records, check it against each account Id 1,2,3 .If they are missingany of these properties, I want to add for that account. In the example above,  I will add DD,XX,YY,ZZ,NN for AccountId 1. I will add BB,CC,DD,YY,ZZ and NN for acount Id 2 and so on.
0
gladfellowCommented:
Assuming 100 is your Global Account Id and there is only one table named 'Profile' to refer in this example, try the following query:

select
	b.AcctId,
	b.Property
from
	Profile a
	right outer join
	(select distinct c.AcctId, d.Property from Profile c cross join Profile d where d.AcctId = 100) b
	on a.AcctId = b.AcctId and a.Property = b.Property
where
	a.AcctId is NULL
order by
	b.AcctId, b.Property

Open in new window


You will get a result set, which would be what has to be inserted into the table. Please check and confirm.

- Venkat
0
RekhaShahAuthor Commented:
Sorry, It did not return any records. Attaching the actual table. Global Id is -1.  I have two buttons: one to sync selected account i eg. 296  with global account or a button to sysnc all the accounts with that of Global account.
ID        Property

296	UD13 REF/NR
297	CrCardNum
296	ExchangeInd
297	UD13 REF/NR
296	AgentStatus
296	Dptr Date
296	BatchStatus
296	Customer
1180	Batch
1180	BatchStatus
1180	CustId
1180	Customer
1180	Dptr Date
1180	ExchangeInd
1180	FailedDataCleared
1180	FullFare 5#S*
1180	UD13 REF/NR
1180	UD10 Split Billing
1180	UD06 GetThere
1180	OnlineTool
1180	Passenger Name
1180	RecordKey
-1	Batch
-1	BatchStatus
-1	CustId
-1	Customer
-1	Dptr Date
-1	ExchangeInd
-1	FailedDataCleared
-1	FullFare 5#S*
-1	UD13 REF/NR
-1	UD10 Split Billing
-1	UD06 GetThere
-1	UD04 Recruit
-1	UD03 Recruit
-1	UD05 Trx Type
-1	Issue Date
-1	PNRLoc
-1	UD7 Low Fare
-1	UD98  Emp ID
NULL	NULL

Open in new window

0
gladfellowCommented:
Hi,

Did you remember to replace 100 in my query (which I had hard coded in my sample for illustrative purpose) with your Global ID -1?

Anyway ... I have attached 'Solution.txt' that contains your data, my suggested query (as mentioned earlier) and your desired results.

Solution.txt

This script will return rows to sync all accounts. To sync a specific account you must include the following condition in the where clause (e.g. 'and b.AcctId = 296')

Trust this helps now.

- Venkat
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RekhaShahAuthor Commented:
You are the best Venkat!  You went an extra mile to create table !!!
I think, I had 'b' instead of 'd' at one place. This is great! Works like a charm. I have so much to learn from GURUs like you. Thank you.
0
gladfellowCommented:
Great to know that the answer worked. The credit goes to great guys who were the architects of SQL as a language. In my experience, I have found that once we express our 'REQUIREMENTS' unambiguously, SQL becomes a natural result (waiting only for an English - SQL translation) ... Having said that, I always refer to SQL more as a SIMPLE QUERY LANGUAGE that is so easy to learn and work with.

Thanks once again for your feedback. Hope to be of more help at this wonderful forum; thanks 'Experts Exchange'!

- Venkat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.