Is there a better way to do this w/o Cursors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Is there a better way to do this w/o Cursors

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-05-21 : 00:30:47

is there any way to do a mass insert and update?

For example I have the following tables

Tb2(AgentID,AddressID,TempAddressID)
Tb3(AddressID(IdentityColumn),Address1,Address2,Address3)
Tb4(TempAddressID,Address1,Address2,Address3)

I am trying to update tb2's AddressID for each record with the IDentity of the inserted rows that corrisponds.

This is not the exact code below, but you should get the picture.

declare rs cursor for
Select TempAddresID from TB2
Open rs
Fetch Next rs into @ID

While @@FETCH_STATUS <> 0

For each record
insert into tb3(Address1,Address2,Address3)
Select Address1,address2,address3)
From TB4
where tb2.TempAddressID = @ID

Update TB2
Set AddressID = Scope_Identity()
Where TempAddressID = @ID
Fetch Next rs into @ID

I know that's not the exact code, but basically I am trying to illustrate that I am Cursor Methodology. I would really like to get it to work using a query statment. Is there anyway to do this without looping through records? Or is there a better way to do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-21 : 00:49:20

[code]insert into TB3(Address1, Address2, Address3)
Select Address1, Address2, Address3)
From TB2 a inner join TB4 b
On a.AgentID = b.TempAddressID[/code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-05-21 : 03:33:28

Thanks for the response, I just noticed that I left out the most important part. I fixed the question to read properly now. Please re-read it, but what I am trying to do is update TB2.AddressID with the Identity of the inserted row from tb4.

Sorry for the confusion. Is there anyway to do this without a cursor? Like a query and subquery?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-21 : 04:37:46

Try this.

insert into TB3(Address1, Address2, Address3)
Select b.Address1, b.Address2, b.Address3
From   TB2 a inner join TB4 b
On     a.TempAddressID = b.TempAddressID

update a
set    a.AddressID = c.AddressID
from   TB2 a inner join TB4 b
       on a.TempAddressID = b.TempAddressID
       inner join TB3 c
       on  c.Address1 = b.Address1
       and c.Address2 = b.Address2
       and c.Address3 = b.Address3

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-05-21 : 15:07:29

quote:

Originally posted by khtan

Try this.

insert into TB3(Address1, Address2, Address3)
Select b.Address1, b.Address2, b.Address3
From   TB2 a inner join TB4 b
On     a.TempAddressID = b.TempAddressID

update a
set    a.AddressID = c.AddressID
from   TB2 a inner join TB4 b
       on a.TempAddressID = b.TempAddressID
       inner join TB3 c
       on  c.Address1 = b.Address1
       and c.Address2 = b.Address2
       and c.Address3 = b.Address3

That would work in most scenerios, but in this one there intentionally may be duplicate address's in the db with differant address key's, is there any way to do it with a key field, and not depend on the address's matching up? I preffer the cursor method because it is 100% because it is a 1-1 key relationship. Maybe my table stucture is wrong, should I use another approach? What I am trying to do is Add addresses outside the production db, then when complete Add it. My actual db is obviously a lot more complicated than what I illustrated, but it's the same principal, It needs to be done in a bulk way too(i.e. the cursor, or the insert from method, then update from method). Any help would be appriciated. The only thing I can think of is to genereate the Identity on the tb4 temp table, then insert it into a non identity field on tb3 master, or Add a field on the tb3 master for the TempAddressID, but then my db structure will not be as clean as I would like.

PLEASE HELP
THANKs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-21 : 21:42:36

Maybe you could explain a bit on your tables ?
What are the tables for ?
It seems that you have duplicated information (Address) across the tables.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-05-21 : 22:08:04

Basically the tables are used in a large User managment system. There can be 50 users at any given time working with the information. What I am looking to accomplish is I want to make sure that All changes to data are handled outside the production tables. After a user opts to save the information, the tmp table will then update the live tables. This is where the issue lies, because the live tables are the ones with the identity fields (I want it to stay this way too), I need to grab the identity at the time of insert to keep the relation. The example I have given is to illustrate the problem I have. By working on all updates/new items in the temp tables, it insures that changes made to the data do not effect the production tables until a user opts to save the items. I used address as a example, but to give you a simpler example that might help illustrate better; A single user might have 30 friends. Each Friend has a address that corresponds with them. The friends are created for the user at the time the user is created from the temp table information that was added for the friends. That's why when I insert from the temp table to the main table I need to keep that relation to the addrID(Identity) in the live table.

I hope this explains what I am looking to accomplish, and why I chose the table structure I did.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-22 : 09:42:57

1. create an identity table in the TB3, say UniqueID.

2. Add the NOT EXISTS IN constraint (if you are allowed to do so)
Example:
insert into TB3(Address1, Address2, Address3)
Select b.Address1, b.Address2, b.Address3
From TB2 a inner join TB4 b
On a.TempAddressID = b.TempAddressID
WHERE NOT EXISTS (
SELECT 1
FROM TB3 c
WHERE
c.Address1 = b.Address1
and c.Address2 = b.Address2
and c.Address3 = b.Address3
)

3. In the update, use the MAX function (please check the syntax)
example:
update a
set a.AddressID = c.AddressID
from TB2 a inner join TB4 b
on a.TempAddressID = b.TempAddressID
inner join TB3 c
on c.Address1 = b.Address1
and c.Address2 = b.Address2
and c.Address3 = b.Address3
WHERE c.UniqueID IN
(
select MAX(UniqueID) MaxUniqueID
FROM TB3 c
WHERE
c.Address1 = b.Address1
and c.Address2 = b.Address2
and c.Address3 = b.Address3
) T

May the Almighty God bless us all!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-05-22 : 15:17:43

I agree with you that this will (likly) work 100% of the time, but when I design a db I like to make sure I can not think of a scenerio where something will not work. Using this method I can. If there are two identical addresses with differant keys in the live db (this is intentional and there is a reason for it), it is possible for the wrong address to be updated. I'm still perfering the cursor method at this point. Please let me know if you agree that for this scenerio the cursor method is going to be the best way.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources