September 13, 2009 at 4:55 am
I have a cursor that is used to take data from one table, convert it, and then insert it into another table. I'd like to do this more efficiently using a set based insert, but I'm having trouble. In particular I'm struggling to work how to fit in the part that increments the sequence.
This is how it appears in the cursor:
set @SEQUENCE = ISNULL( (SELECT MAX([SEQUENCE]) FROM SUMMARY_TABLE WHERE PERSON_ID = @PERSON_ID), 0 ) + 1
Can anyone give me some help on how to approach this?
Heres the cursor:
DECLARE @PERSON_ID CHAR(7) , @ORDER_DETAILS VARCHAR(255), @SEQUENCE INT
DECLARE TRANSFERCUR CURSOR FOR
SELECT PERSON_ID , ORDER_DETAILS
FROM ORDERS
OPEN TRANSFERCUR
FETCH NEXT FROM TRANSFERCUR INTO @PERSON_ID , @ORDER_DETAILS
WHILE @@FETCH_STATUS = 0
BEGIN
set @SEQUENCE = ISNULL( (SELECT MAX([SEQUENCE]) FROM SUMMARY_TABLE WHERE PERSON_ID = @PERSON_ID), 0 ) + 1
INSERT INTO SUMMARY_TABLE ( PERSON_ID , [SEQUENCE] , DETAILS )
VALUES (@PERSON_ID , @SEQUENCE , @ORDER_DETAILS )
FETCH NEXT FROM TRANSFERCUR INTO @PERSON_ID , @ORDER_DETAILS
END
CLOSE TRANSFERCUR
DEALLOCATE TRANSFERCUR
Here's all the other stuff:
CREATE TABLE [dbo].[ORDERS] (
[ORDER_ID] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PERSON_ID] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ORDER_DETAILS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ORDERS] WITH NOCHECK ADD
CONSTRAINT [PK_ORDERS] PRIMARY KEY CLUSTERED
(
[ORDER_ID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_ORDERS] ON [dbo].[ORDERS]([ORDER_ID]) ON [PRIMARY]
GO
CREATE INDEX [IX_ORDERS_1] ON [dbo].[ORDERS]([PERSON_ID]) ON [PRIMARY]
GO
-------
INSERT INTO ORDERS
VALUES('0000035','0022222','Test Details 1')
INSERT INTO ORDERS
VALUES('0038944','0033333','Test Details 2')
INSERT INTO ORDERS
VALUES('0007684','0022222','Test Details 3')
INSERT INTO ORDERS
VALUES('0003343','0033333','Test Details 4')
-------
CREATE TABLE [dbo].[SUMMARY_TABLE] (
[PERSON_ID] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SEQUENCE] [int] NOT NULL ,
[DETAILS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SUMMARY_TABLE] WITH NOCHECK ADD
CONSTRAINT [PK_SUMMARY_TABLE] PRIMARY KEY CLUSTERED
(
[PERSON_ID],
[SEQUENCE]
) ON [PRIMARY]
GO
September 14, 2009 at 11:44 am
There's probably a better way to do this, but this should get you started. Use COLLATEs on the temp table create if needed. Probably should add an index on the temp table. WARNING-- if you are loading lots of data it may be more appropriate to use a permanent table.. but if this code may be run by anyone at any time (opposed to a controlled or scheduled run) you'd THEN need to be sure 2 users running at the same time didn't bump into one another.
If you were using SQL2005 this could be simplified with ROW_NUMBER (see BOL)...you'd be able to do it all in a single INSERT statement.
-- note the identity column
CREATE TABLE #INTERIM_SUMMARY_TABLE(
rowid INT IDENTITY,
person_id char(7) not null,
sequence int null,
details varchar(255) null
)
-- you must use this ORDER BY
INSERT INTO #INTERIM_SUMMARY_TABLE
SELECT PERSON_ID,NULL,ORDER_DETAILS
FROM ORDERS
ORDER BY PERSON_ID
-- and this sets SEQUENCE
UPDATE target
SET SEQUENCE = 1 + target.rowid - minrow.minrow
FROM
#INTERIM_SUMMARY_TABLE target
INNER JOIN
(
SELECT MIN(rowid) minrow,person_id
FROM #INTERIM_SUMMARY_TABLE
GROUP BY person_id
) minrow
ON target.person_id = minrow.person_id
-- then load it into your real table
INSERT INTO SUMMARY_TABLE
SELECT person_id,sequence,details
FROM #INTERIM_SUMMARY_TABLE
--ORDER BY person_id,sequence-- if needed
-- cleanup
DROP TABLE #INTERIM_SUMMARY_TABLE
June 28, 2023 at 2:27 pm
Hello.
I need some help. I need to convert this code with cursors to set-based operations. I can't understand how I can make the set-based code do the same as with cursors.
DECLARE Cursor static local FOR
SELECT Cid,Abrev FROM EntityType
OPEN Cur
FETCH NEXT FROM Cur INTO @entityType, @entityTypeAbrev
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CurEnt = Cursor static local FOR
SELECT did FROM Entity where EntityTypeCid=@entityType
OPEN @CurEnt
FETCH NEXT FROM @CurEnt INTO @entityId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RandNumber= FLOOR(RAND()*(28-1)+1);
select @entityNumber=EntityNumber from Entity
where did=@entityId
--print @entityNumber
SET @FullName= @entityTypeAbrev + ' ' + cast(@entityNumber as nvarchar)
SET @ShortName = @FullName
/*
* ENTITY*/
UPDATE entity set fullname=@FullName, ShortName=@ShortName where did=@entityId
UPDATE Agenda set EntityName= @FullName where EntityDid=@entityId
--Intervetions
SET @CurIntervs = Cursor static local FOR
select did, CredOperIntervType from CredOperInterv where EntityDid=@entityId
OPEN @CurIntervs
FETCH NEXT FROM @CurIntervs INTO @credOperIntervDid, @credOperIntervTypeId
WHILE @@FETCH_STATUS = 0
BEGIN
Thanks in advance
Paulo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply