RekhaShah
asked on
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
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
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:
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
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
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
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.
I'd be curious to see whether there is any meaningful difference in run time for the two approaches.
ASKER
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.
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.
Assuming 100 is your Global Account Id and there is only one table named 'Profile' to refer in this example, try the following query:
You will get a result set, which would be what has to be inserted into the table. Please check and confirm.
- Venkat
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.PropertyYou will get a result set, which would be what has to be inserted into the table. Please check and confirm.
- Venkat
ASKER
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 NULLASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
Thanks once again for your feedback. Hope to be of more help at this wonderful forum; thanks 'Experts Exchange'!
- Venkat
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