Updating an identity column sql server
I added ID’s to Data Vault 2.0 hubs to use for SCD Type 1 Dimensions.
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 17 ms. Scan count 1, logical reads 23751, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
In this step we are doing an UPDATE to the table and for each row that is updated we are updating the variable by 1 as well as the id column in the table.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION DECLARE @id INT SET @id = 0 UPDATE accounts2 SET @id = id = @id 1 COMMIT TRANSACTION Once you have created an identity column there is no easy way to renumber all of your values for each row.
With the update approach you could do this over and over again by just rerunning the query and changing the values.
The flexibility of bi GENi US made it possible for me to change the template for the generated hub, and use a column that gets a value from a SEQUENCE instead of an IDENTITY column.
CREATE TABLE [dbo].[tm2] ( [id] [int] NOT NULL, [aid] [int] IDENTITY(1,1) NOT NULL, [txt] [nchar](10) NULL, CONSTRAINT [PK_tm2] PRIMARY KEY CLUSTERED ([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].tm2 ON MERGE INTO [dbo].tm2 AS Target USING (VALUES (1,2,'qdqewqf'), (2,3,'#ED7F00') ) AS Source ([ID], [aid], [txt]) ON (Target.[ID] = Source.[ID]) WHEN MATCHED AND () THEN UPDATE SET aid = Source.aid, txt = WHEN NOT MATCHED BY TARGET THEN INSERT([ID], aid, txt) VALUES(Source.[ID], Source.aid, Source.txt) WHEN NOT MATCHED BY SOURCE THEN DELETE; SET IDENTITY_INSERT [dbo].tm2 OFF USE tempdb; CREATE TABLE dbo.
But in my case I generated a data warehouse house with bi GENi US, a data warehouse automation tool.I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number.In addition to adding the column, I also need to populate the existing records with an incremental counter what options are there to do this?SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. Scan count 1, logical reads 26384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 4781 ms, elapsed time = 4856 ms.(100000 row(s) affected)SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.Then I did an initial load, and then I wanted to set some ID’s right.