August 14, 2013 at 12:36 pm
Hi !
I hope that you can show me how to do it avoiding using loop. I need to group records imported from flat file by ID. Column LegacyID is identity column. I need new ID incremented on next value='H'. Thank you.Mark Gorelik
create table LoadTest (legacyid int not null ,field1 varchar(5) not null, newid int null)
insert into LoadTest (legacyid ,field1)
SELECT 2, 'H'
UNION ALL
SELECT 3, 'C'
UNION ALL
SELECT 4, 'S'
UNION ALL
SELECT 5, 'O'
UNION ALL
SELECT 6, 'I'
UNION ALL
SELECT 7, 'I'
UNION ALL
SELECT 8, 'I'
UNION ALL
SELECT 9, 'H'
UNION ALL
SELECT 10, 'C'
UNION ALL
SELECT 11, 'S'
UNION ALL
SELECT 12, 'O'
UNION ALL
SELECT 13, 'I'
UNION ALL
SELECT 14, 'I'
UNION ALL
SELECT 15, 'I'
UNION ALL
SELECT 16, 'I'
UNION ALL
SELECT 17, 'I'
UNION ALL
SELECT 18, 'I'
UNION ALL
SELECT 19, 'N'
UNION ALL
SELECT 20, 'H'
UNION ALL
SELECT 21, 'C'
UNION ALL
SELECT 22, 'S'
UNION ALL
SELECT 23, 'O'
UNION ALL
SELECT 24, 'I'
UNION ALL
SELECT 25, 'I'
SELECT * FROM LoadTest
legacyid field1 newid
======================================
2 H 1
3 C 1
4 S 1
5 O 1
6 I 1
7 I 1
8 I 1
9 H 2
10 C 2
11 S 2
12 O 2
13 I 2
14 I 2
15 I 2
16 I 2
17 I 2
18 I 2
19 N 2
20 H 3
21 C 3
22 S 3
23 O 3
24 I 3
25 I 3
August 14, 2013 at 12:57 pm
An easy and effective way to do this is by using the quirky update. You must fully understand what it is doing, follow all the rules and test for effectiveness and correct data.
This article explains it completely http://qa.sqlservercentral.com/articles/T-SQL/68467/
And here's a sample code based on your data.
CREATE CLUSTERED INDEX IX_LoadTest_LegacyID ON LoadTest( LegacyID)
DECLARE @New_ID int = 0, @anchor int
UPDATE LoadTest WITH( TABLOCKX) SET
@anchor = LegacyID,
@New_ID = NEWID = @New_ID + CASE WHEN field1 = 'H' THEN 1 ELSE 0 END
OPTION( MAXDOP 1)
SELECT *
FROM LoadTest
August 14, 2013 at 2:24 pm
Thank you Luis !
I tested your solution on real data and for some reason it does not work . ID incremented for field1 value='H' .The rest script updated with one value=209 except field1 ='C' . Value "C" updated with zero. I should say that script worked on submitted test data.Please see attached result.
August 14, 2013 at 2:52 pm
Mark Gorelik (8/14/2013)
Thank you Luis !I tested your solution on real data and for some reason it does not work . ID incremented for field1 value='H' .The rest script updated with one value=209 except field1 ='C' . Value "C" updated with zero. I should say that script worked on submitted test data.Please see attached result.
My guess is that in your real table you don't have a clustered index on legacyID. The rules for the quirky update are very specific and it will not work correctly unless you follow ALL of them.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 14, 2013 at 3:32 pm
It does the same with cluster index . must be something else.
August 14, 2013 at 3:42 pm
Could you post the actual table definition (table and column names changed if necessary) with the clustered index and the code you're using?
August 14, 2013 at 3:43 pm
Sean Lange (8/14/2013)
Mark Gorelik (8/14/2013)
Thank you Luis !I tested your solution on real data and for some reason it does not work . ID incremented for field1 value='H' .The rest script updated with one value=209 except field1 ='C' . Value "C" updated with zero. I should say that script worked on submitted test data.Please see attached result.
My guess is that in your real table you don't have a clustered index on legacyID. The rules for the quirky update are very specific and it will not work correctly unless you follow ALL of them.
Then can you post your actual DDL for the table and the entire code you executed?
--Edit--
Luis beat me to it. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 14, 2013 at 3:52 pm
****** Object: Table [dbo].[tdsMSMCA_Askk] Script Date: 08/14/2013 17:45:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tdsMSMCA_Askk](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Field1] [varchar](255) NULL,
[Field2] [varchar](255) NULL,
[Field3] [varchar](255) NULL,
[Field4] [varchar](255) NULL,
[Field5] [varchar](255) NULL,
[Field6] [varchar](255) NULL,
[Field7] [varchar](255) NULL,
[Field8] [varchar](255) NULL,
[Field9] [varchar](255) NULL,
[Field10] [varchar](255) NULL,
[Field11] [varchar](255) NULL,
[Field12] [varchar](255) NULL,
[Field13] [varchar](255) NULL,
[Field14] [varchar](255) NULL,
[Field15] [varchar](255) NULL,
[Field16] [varchar](255) NULL,
[Field17] [varchar](255) NULL,
[Field18] [varchar](255) NULL,
[Field19] [varchar](255) NULL,
[Field20] [varchar](255) NULL,
[Field21] [varchar](255) NULL,
[Field22] [varchar](255) NULL,
[Field23] [varchar](255) NULL,
[Field24] [varchar](255) NULL,
[DelFlag] [smallint] NULL,
[NewID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [IX_LoadTest_LegacyID] Script Date: 08/14/2013 17:45:49 ******/
CREATE CLUSTERED INDEX [IX_LoadTest_LegacyID] ON [dbo].[tdsMSMCA_Askk]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [ind_tdsMSMCA_askk_0] Script Date: 08/14/2013 17:45:49 ******/
CREATE NONCLUSTERED INDEX [ind_tdsMSMCA_askk_0] ON [dbo].[tdsMSMCA_Askk]
(
[Field1] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
DECLARE @New_ID int = 0, @anchor int
UPDATE dbo.tdsMSMCA_Askk WITH( TABLOCKX)
SET @anchor = ID,
@New_ID = NEWID = @New_ID + CASE WHEN field1 = 'H' THEN 1 ELSE 0 END
OPTION( MAXDOP 1)
==================================
---Loop that works
DECLARE
@wt_row_id INT
,@last_@wt_row_id INT
, @cookie_id varchar(5)
, @session_id INT
SELECT @session_id = 1,@last_@wt_row_id=0
DECLARE cur CURSOR FOR
SELECT
id,
field1
FROM tdsMSMCA_Askk ORDER BY id
OPEN cur
FETCH NEXT FROM cur INTO
@wt_row_id,
@cookie_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF @last_@wt_row_id < @wt_row_id
BEGIN
IF @cookie_id ='H'
BEGIN
SET @session_id = @session_id + 1
END
SET @last_@wt_row_id= @wt_row_id
UPDATE tdsMSMCA_Askk
SET [NewID] = @session_id
where id=@wt_row_id
END
FETCH NEXT FROM cur INTO @wt_row_id, @cookie_id
END
CLOSE cur
DEALLOCATE cur
August 14, 2013 at 4:22 pm
A solution composed just before bedtime, and it could serve from further testing:
;WITH CTE AS (
SELECT legacyid, field1, newid,
row_number() OVER(ORDER BY legacyid) as rowno,
row_number() OVER(ORDER BY CASE field1 WHEN 'H' THEN 1 ELSE 2 END, legacyid) AS H_no
FROM LoadTest
), CTE2 AS (
SELECT legacyid, field1, newid,
CASE field1 WHEN 'H' THEN H_no
ELSE dense_rank() OVER (ORDER BY CASE WHEN field1 <> 'H' THEN rowno - H_no ELSE 1000000 END )
END AS new_newid_value
FROM CTE
)
UPDATE CTE2
SET newid = new_newid_value
go
SELECT * FROM LoadTest ORDER BY legacyid
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 15, 2013 at 2:25 am
SELECT
l.legacyID,
l.field1,
[Newid] = DENSE_RANK() OVER(ORDER BY x.legacyID)
FROM #LoadTest l
OUTER APPLY (
SELECT TOP 1 legacyID
FROM #LoadTest i
WHERE i.field1 = 'H'
AND i.legacyID <= l.legacyID
ORDER BY i.legacyID DESC) x
ORDER BY l.legacyID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 15, 2013 at 12:22 pm
Thank you . It works .
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply