Requires T-SQL update statement to dynamically assign record number
I have a dataset that I got from XML and it crashed with the following structure:
[Data table]
[ID] [Name] [Value]
1 ad1_pk 1
2 ad1_addr1 123 Easy Street
3 ad1_pk 2
4 ad1_addr1 99 US31
5 ad1_atfk 6
6 ad1_pk 3
... {and so on}
I added a column (called recNum) to indicate a separate record number; however, I haven't found a quick way to set the record number for each record. The number of lines indicating an individual record can change, so I want the update statement to be able to handle this. Each "record" has a column name that ends with "_pk", so I define the start of each record in the dataset.
I did it successfully with a while loop, but it is too slow and tables can have millions of records. Example:
DECLARE @maxRowID INT = (SELECT MAX(ID) FROM myTable)
DECLARE @i INT = 1
DECLARE @currentRecordID INT = 1
WHILE @i<@maxRowID AND @i<100 BEGIN
IF (SELECT RIGHT(name,3) [name] FROM myTable WHERE ID=@i)='_pk' AND @i>1 BEGIN
SET @currentRecordID = (SELECT DISTINCT value FROM myTable WHERE id=@i)
RAISERROR('Record=%i',0,1,@currentRecordID) WITH NOWAIT
UPDATE z2
SET recNum=@currentRecordID
FROM myTable z2
WHERE id=@i
END ELSE BEGIN
UPDATE z2
SET recNum=@currentRecordID
FROM myTable z2
WHERE id=@i
END
SET @i = @i+1
END
Does anyone have a suggestion to do this quickly, without using a cursor? My ultimate goal is to insert statements into a SQL table (already created) in the following format:
insert into myNewTable ({column name list}) VALUES ({value list})
... [updated: 2015-06-24 00:26 EDT] This is how I got it so far ... https://drive.google.com/file/d/0B82UP-AIFz_ITlNIb1ZwSFdyODg/view?usp=sharing
SELECT TOP 100
z2.ID,z2.Name,z2.Value,CASE WHEN z2.ID=RecIDs.ID THEN z2.Value ELSE NULL END RecNum
FROM MyTable 2
LEFT JOIN (
SELECT DENSE_RANK() OVER (ORDER BY ID) drn,ID FROM MyTable
WHERE RIGHT(name,3)='_pk'
) RecIDs ON RecIDs.ID = z2.ID
ORDER BY ID
... I need to fill in the blanks. Any suggestions?
[updated: 2015-06-25 09:33 EDT] This is for SQL Server 2008 R2
source to share
First, you want to assign a value RecNum
to everyone PK
using ROW_NUMBER
. After that, you want to update the remaining lines with the appropriateRecNum
WITH CtePKs AS(
SELECT *,
RN = ROW_NUMBER() OVER(ORDER BY ID)
FROM z2
WHERE RIGHT(Name, 3) = '_pk'
)
UPDATE CtePKs SET RecNum = RN
UPDATE z
SET RecNum = x.RecNum
FROM z2 z
OUTER APPLY(
SELECT TOP 1 Id, RecNum
FROM z2
WHERE
ID < z.ID
AND RecNum IS NOT NULL
ORDER BY ID DESC
)x
WHERE z.RecNum IS NULL
RESULT
| ID | Name | Value | RecNum |
|----|-----------|-----------------|--------|
| 1 | ad1_pk | 1 | 1 |
| 2 | ad1_addr1 | 123 Easy Street | 1 |
| 3 | ad1_pk | 2 | 2 |
| 4 | ad1_addr1 | 99 US31 | 2 |
| 5 | ad1_atfk | 6 | 2 |
| 6 | ad1_pk | 3 | 3 |
source to share
If I understand your question correctly, you can use a correlated subquery like this. If you are using SQL Server 2012 or higher you can also use SUM() OVER()
. Check Change.
Sample data
CREATE TABLE Table1
([ID] int,rowid int, [Name] varchar(9), [Value] varchar(15));
INSERT INTO Table1
([ID], [Name], [Value])
VALUES
(1, 'ad1_pk', '1'),
(2, 'ad1_addr1', '123 Easy Street'),
(3, 'ad1_pk', '2'),
(4, 'ad1_addr1', '99 US31'),
(5, 'ad1_atfk', '6'),
(6, 'ad1_pk', '3');
Query
UPDATE Table1
SET rowid =
(SELECT COUNT(ID) FROM Table1 T2 WHERE T2.ID <= Table1.ID AND T2.Name Like '%[_]pk');
SELECT * FROM Table1;
OUTPUT
| ID | rowid | Name | Value |
|----|-------|-----------|-----------------|
| 1 | 1 | ad1_pk | 1 |
| 2 | 1 | ad1_addr1 | 123 Easy Street |
| 3 | 2 | ad1_pk | 2 |
| 4 | 2 | ad1_addr1 | 99 US31 |
| 5 | 2 | ad1_atfk | 6 |
| 6 | 3 | ad1_pk | 3 |
EDIT
For SQL Server 2012 or higher
Query
;WITH CTE AS
(
SELECT SUM(CASE WHEN Name LIKE '%[_]pk' THEN 1 ELSE 0 END) OVER(ORDER BY ID) recnum,*
FROM Table1
)
UPDATE CTE
SET rowid = recnum;
SELECT * FROM Table1;
source to share
You can use cte for this:
;With cte as (
SELECT [id], Row_number() OVER(Order by [id] As rn
FROM MyTable
)
UPDATE MyTable
SET recNum = rn
FROM MyTable t
INNER JOIN cte ON(t.[id] = cte.[id])
However, you already have an id column that appears to have the values you are asking for, you can simply do this:
UPDATE MyTable
SET recNum = [id]
source to share