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
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply