How to replace this cursor? - SQL Server Forums

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)

How to replace this cursor?

Author

Topic

SQLTim
Starting Member

21 Posts

Posted - 2005-06-10 : 19:33:20

I'm trying to convert this code into anything other than a cursor. There are definately some basics I'm missing.

In short, empty tables are being populated with data from transactional tables to create historical logs.
I'd like to simply select this data into the tr_info table but the primary keys are not identity based, but based on an incrementing key from a sequence table (grabbed by an SP Ssp_get_unique_key) and a site id.

How would I create this "key" on the fly without using a cursor?
This happens 12 seperate times for 12 tables

SET NOCOUNT ON

-- These variables are used with cursors each step
DECLARE @Suffix CHAR(6)
DECLARE @NextKey INTEGER
DECLARE @PriKey NUMERIC(17)
DECLARE @TransNo NUMERIC(17)
DECLARE @SaleNo NUMERIC(17)
DECLARE @InfoType TINYINT
DECLARE @InfoNum VARCHAR(16)
DECLARE @GuestNo NUMERIC(17)
DECLARE @AdditInfo VARCHAR(10)

SET @Suffix = (SELECT RIGHT('000000' + CAST(site_no AS VARCHAR(3)), 6) FROM prefs)

--table used to get value of stired proc siriussp_get_unique_key
CREATE TABLE #TempUKey (next_key INT)

-- TYPE 10 "GUEST_NO"
-- Create type (10) "GUEST_NO" records in tr_info table for guest records created by a line item in a sale
DECLARE myCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY TYPE_WARNING FOR
SELECT t.trans_no, t.sale_no, 10 AS info_type,
CAST(g.guest_no AS CHAR(17)) AS info_num, g.guest_no, '' AS addit_info
FROM guests g
JOIN transact t ON g.trans_no=t.trans_no
WHERE NOT EXISTS
(SELECT i.pri_key
FROM tr_info i
WHERE i.trans_no=t.trans_no AND i.info_type=10 AND i.info_num=CAST(g.guest_no AS CHAR(17)))

OPEN myCursor
--PRINT 'Ready to Insert ' + CAST(@@CURSOR_ROWS AS VARCHAR(16)) + ' Rows Into TR_INFO For Type 10 "GUEST_NO" ' + CAST(GETDATE() AS VARCHAR(20))
IF @@CURSOR_ROWS > 0
BEGIN
DELETE FROM #TempUKey
INSERT #TempUKey (next_key)
EXEC Ssp_get_unique_key 'K_TR_INFO', @@CURSOR_ROWS
SET @NextKey = (SELECT next_key FROM #TempUKey)

FETCH NEXT FROM myCursor INTO @TransNo, @SaleNo, @InfoType, @InfoNum, @GuestNo, @AdditInfo
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PriKey = CAST(CAST(@NextKey AS VARCHAR(16)) + @Suffix AS NUMERIC(17))
INSERT INTO dbo.tr_info
(pri_key, trans_no, sale_no, info_type, info_num, guest_no, addit_info)
VALUES
(@PriKey, @TransNo, @SaleNo, @InfoType, @InfoNum, @GuestNo, @AdditInfo)

FETCH NEXT FROM myCursor INTO @TransNo, @SaleNo, @InfoType, @InfoNum, @GuestNo, @AdditInfo
SET @NextKey = @NextKey + 1
END
END
CLOSE myCursor
DEALLOCATE myCursor

Many thanks in advance!!
Tim

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-10 : 21:28:13

can you give us a sample of what data you are starting with and what you want it to look like at the end. I'm notoriously bad at reading cursors..

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

SQLTim
Starting Member

21 Posts

Posted - 2005-06-10 : 23:12:03

Say I'm going to select from one table some records that I want to insert into another table.
I'm going to recreate the guest history for each guest. In this eample, it just the fact that a guest record was created.
Every guest record would have a record in tr_info with a "Guest Created" record. (there are many other types but this will do for now)

The problem is, that second table (tr_info) has a primary key I need to populate with a composite value from other sources. (a sequence and a site_id in a prefs table)
The sequences table is just a stripe table of keys and "buckets" or numbers.
I use a SP to find the key, bump up the number in there and return to me the next number.
The key would look like CAST(CAST(@NextKey AS VARCHAR(16)) + @site_id AS NUMERIC(17))
The site_id never changes for this whole run.

So let's say the first value from the sequences table is 1 and the site_id is 5, the pri_key would be 10005.
The next number would be 20005, then 30005, the 12345th number would be 123450005.

The cursor above exists only to be able to insert a different key value in the primary key of the tr_info table.
Is there a way I can do this without using a cursor?

To restate
Select from TableA into TableB but provide a composite primary key in TableB from concatenating two other tables (incrementing one of those tables)

I hope I haven't lost you! <g>

Thanks for your help!
Tim

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-11 : 12:23:41

I'm still a little confused as to why you want to do this, but here is a simple example of what I think you are trying to do. Hope it helps.


Create Table #origData (siteId int, otherData varchar(10))
Insert Into #origData
Select 5, 'blah1' Union All
Select 5, 'blah2' Union All
Select 5, 'blah3' Union All
Select 5, 'blah4' Union All
Select 5, 'blah5' Union All
Select 5, 'blah6' Union All
Select 5, 'blah7'

--Select * From #origData

Create Table #myTable (new_Id int identity(1,1), SiteId int, otherData varchar(10))
Insert Into #myTable Select SiteId, otherData From #origData

--Select * From #myTable

Select compKey = convert(varchar,new_Id) + right(replicate('0',4)+convert(varchar,SiteId),4),
	new_Id,
	SiteId,
	OtherData
From #myTable

Drop Table #myTable
Drop Table #origData

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

SQLTim
Starting Member

21 Posts

Posted - 2005-06-12 : 17:28:31

-- The Prefs table has only one record and has defaults for the whole database.
-- Most would never have anything other than a site_id of 1
-- However, if you were a multi-site client, each site would have a different number.
Create Table #prefs (site_id int)
Insert Into #prefs
Select 5

-- This is our primary key table. It holds a record for certain primary keys in our database.
-- The name field is the primary key field name, and the next_val holds the next number to be used for that key.

Create Table #sequences (name varchar(10),next_val int)
Insert Into #sequences
Select 'TR_INFO',1

-- We use an SP to lock the field, grab the next_val and increment the field.
-- It can also let us pull over a range of numbers.

CREATE PROCEDURE sp_get_unique_key
@tcKeyName char(10),
@tnNumKeys int
WITH RECOMPILE
AS
set nocount on
if not exists (select name from sequence where name=@tcKeyName)
insert into sequence (name, next_val) values(@tcKeyName, 1)
begin transaction
update sequence with (rowlock holdlock) set next_val=next_val+@tnNumKeys where name=@tcKeyName
select next_val - @tnNumKeys as next_val from sequence where name=@tcKeyName
commit transaction
GO

-- This is just a standard guests table with most of the info only fields missing.
-- One records exists for each guest in our system.
CREATE TABLE #guests (
[guest_no] [numeric](17, 0),
[name] [varchar] (20) )

insert into #guests
select 1,'Frank' union all
select 2,'Steph ' union all
select 3,'Jean ' union all
select 4,'Lloyd '

-- TR_Info is a guest history table that holds a record for each kind of activity we could have with a guest.
-- The differnt types of records are coded with info_type: 10 - new guest, 20 New Pass, 30 Booking, etc

CREATE TABLE #tr_info (
[pri_key] [numeric](17, 0),
[info_type] [tinyint] ,
[guest_no] [numeric](17, 0) )

-- In this example, I'm trying to recreate the tr_info table based on data that already exists in the system.
-- So if I were to select every record from guests into tr_info, that would be ok with me like...
-- Insert into #tr_info
-- select ????,10,guest_no from guests

-- So, could I do something like this?

SET @Suffix = (SELECT RIGHT('000000' + CAST(site_id AS VARCHAR(3)), 6) FROM #prefs)

Insert into #tr_info
select CAST(CAST(EXEC dbo.sp_get_unique_key AS VARCHAR(16)) + @Suffix AS NUMERIC(17)), 10, guest_no from guests

A little info..
We are trying to shave hours off of what is already a 9 hour conversion. One of our clients has several different sites that we have to convert all in one evening ready for live the next morning. <g>
This one process takes about 2 hours. Looking for some caffeine <bg>

Thank you for your help on this!!

SQLTim
Starting Member

21 Posts

Posted - 2005-06-13 : 18:07:49

I'm sorry if I have lost you. I'll try to restate.

------------------------------------------------
-- Just some table & procedure definitions
------------------------------------------------
-- An SP we use to retrieve the next number.
-- If the Name does not exist, it created it and gives it a staring value of one.
-- It can also return a range of numbers
CREATE PROCEDURE sp_get_unique_key
@tcKeyName char(10),
@tnNumKeys int
WITH RECOMPILE
AS
set nocount on
if not exists (select name from #sequence where name=@tcKeyName)
insert into #sequence (name, next_val) values(@tcKeyName, 1)
begin transaction
update #sequence with (rowlock holdlock) set next_val=next_val+@tnNumKeys where name=@tcKeyName
select next_val - @tnNumKeys as next_val from #sequence where name=@tcKeyName
commit transaction
GO

-- A prefs table with just the site id in it.
Create Table #prefs (site_id int)
Insert Into #prefs
Select 5

-- A sequence table.
-- One record for each primary key field that isn't identity based.
-- i.e. K_TR_INFO is the primary key field for the TR_INFO table
Create Table #sequence (name varchar(10),next_val int)

-- This is just a standard guests table with most of the info only fields missing.
-- One records exists for each guest in our system.
CREATE TABLE #guests (
[guest_no] [numeric](17, 0),
[name] [varchar] (20) )

insert into #guests
select 1,'Frank' union all
select 2,'Steph ' union all
select 3,'Jean ' union all
select 4,'Lloyd '

-- TR_Info is a guest history table that holds a record for each kind of activity we could have with a guest.
-- The differnt types of records are coded with info_type: 10 - new guest, 20 New Pass, 30 Booking, etc

CREATE TABLE #tr_info (
[pri_key] [numeric](17, 0),
[info_type] [tinyint] ,
[guest_no] [numeric](17, 0) )

------------------------------------------------
-- The heart of the matter
------------------------------------------------

-- My goal is to populate this the tr_info table from the guests table.
-- One record for each guest.
-- If the pri_key here were an identity field, all I would have to do is this:

Insert into #tr_info
select 10,guest_no from guests

-- But it isn't an identity field. We pull our numbers from the sequence table above via a stored procedure
-- Then we tack on a site id from the prefs table, '000005' in this example.
-- The site_id does not change for the entire run, but the sequence does of course.
-- So the first 5 numbers would look like: 1000005, 2000005, 3000005, 4000005, 5000005...

-- I would LOVE to do something like this:

Insert into #tr_info
select
CAST(CAST( EXEC sp_get_unique_key('K_TR_INFO',1) AS VARCHAR(16)) + @Suffix AS NUMERIC(17)),
10,
guest_no
from guests

-- What am I doing wrong?
-- How can I find a way to do this without resorting to a cursor?

-- I was thinking about creating a temp table with an identity field that starts at the value I need it to start
-- at, insert into that, then insert from there into the tr_info table creating the composite key from this.
-- But it seems like the above is doable is some way.

I really appreciate any ideas, this one is got me stumped.

Many thanks in advance!
Tim

SQLTim
Starting Member

21 Posts

Posted - 2005-06-14 : 11:57:30

I found one way of doing it!
Thought I'd put it in here for review and possible help for others.
It cut the time from 1hr 15min down to 16min for 2.8M records.

This will refer to the first example I posted in this thread using a cursor.

In Short:
- I create a temp table with an identity field and populate that with the results.
- Then knowing how many records are going to be inserted, I grab that many keys in
a single call to the sp_get_unique_key SP.
- Then use a combination of the site suffix and the identity field and I have the
composite field I'm looking for inserted as the primary key.
- Now that I think of it, I can just skip the update part and do that compositing
on the way in to the real tr_info table.

-------------------------------------------------
DECLARE @Suffix CHAR(6)
DECLARE @NextKey INTEGER
DECLARE @NumRows INTEGER

SET @Suffix = (SELECT RIGHT('000000' + CAST(site_no AS VARCHAR(3)), 6) FROM prefs)

--table used to get value of stired proc sp_get_unique_key
CREATE TABLE #TempUKey (next_key INT)

-- Used as temporary staging table
CREATE TABLE #tr_info (
[IncKey] [int] IDENTITY (1, 1) ,
[pri_key] [numeric](17, 0) ,
[trans_no] [numeric](17, 0),
[sale_no] [numeric](17, 0) ,
[info_type] [tinyint] ,
[info_num] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS ,
[guest_no] [numeric](17, 0) ,
[addit_info] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS )

-- TYPE 10 "GUEST_NO"
-- Create type (10) "GUEST_NO" records in tr_info table for guest records created by a line item in a sale
-- Get count
insert into #tr_info
SELECT t.trans_no, t.sale_no, 10 AS info_type,
CAST(g.guest_no AS CHAR(17)) AS info_num, g.guest_no, '' AS addit_info
FROM guests g
JOIN transact t ON g.trans_no=t.trans_no
WHERE NOT EXISTS
(SELECT i.pri_key
FROM tr_info i
WHERE i.trans_no=t.trans_no AND i.info_type=10 AND i.info_num=CAST(g.guest_no AS CHAR(17)))

IF @@ROWCOUNT > 0
BEGIN
PRINT CAST(GETDATE() AS VARCHAR(20))+' - '+'Ready to Insert ' + CAST(@@ROWCOUNT AS VARCHAR(16)) + ' Rows Into TR_INFO '
INSERT #TempUKey (next_key)
EXEC dbo.sp_get_unique_key 'K_TR_INFO', @@ROWCOUNT
SET @NextKey = (SELECT next_key FROM #TempUKey) - 1

update #tr_info
set pri_key = CAST(CAST( t.IncKey+@NextKey AS VARCHAR(16)) + @Suffix AS NUMERIC(17))
from #tr_info t
PRINT CAST(GETDATE() AS VARCHAR(20))+' - '+'Updated prikey '

INSERT INTO dbo.tr_info
(pri_key, trans_no, sale_no, info_type, info_num, guest_no, addit_info)
select pri_key, trans_no, sale_no, info_type, info_num, guest_no, addit_info from #tr_info
END

There are about 8 other selects similar to this won so I just UNION ALL them and tada! <g>

Thanks for being a sounding board!

Tim

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-14 : 13:30:31

Sorry I disappeared Tim, its been a bit of a hectic few days down here...

anyway, glad you got something working, though I still don't understand the 'get_unique_key' deal...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

SQLTim
Starting Member

21 Posts

Posted - 2005-06-14 : 14:07:59

There is a table that holds the next key value for certain primary fields in our database.
get_unique_key is a SP that gaurantees that you and only you get the next value, then increments the value in the table.
Say your guests, sale_hdr, transact, and tr_info have non identity primary keys like this.
After populating one record for each table, this sequence table would look like:
name, next_val
'K_GUESTS', 1
'K_SALE_HDR', 1
'K_TRANSACT', 1
'K_TR_INFO', 1

Because we have the same database at different sites for the same client, we have one field in the system that is the site_id.
From these two sources we create a composite primary key that will not conflict with the primary key in any of the other sited should they be merged in reporting.
So the pri_key of the first record of the guests table for site # 5 be 1000005.
Creating this composite key on an insert was the problem I was facing.
The person before me wrote this with cursors and it was taking way too long.

Turns out that to explain it is harder than the problem is difficult. <g>
Sorry if I was unclear.

Thanks,
Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources