Inserting rows from 1 to n in the table.
-
Hi
I need to generate rows from 1 to n to an existing table but the new column to added shall not be with Identity property also I don't want to make use of ROW_NUmber () functionality (coz of some business constraints) in Yukon.
e.g : say i have table xyz (ID , name , ....) with rowcount = 1 Mil, now i want to add another col to table that will populate rec from 1 to 1mil and this col shall not be identity.
Regards
Shrikant Kulkarni -
You can use a temporary table for that.
select id, identity(int, 1, 1) as row_num
into #temp
from existing_table
Once that is done, you can update the existing_table's new column with the row_num value by doing a join. In the above example, the code assumes that ID is the primary key column for the "existing_table"...if that is not the case, when creating the temp table, specify the primary key column(s) so that later on you could join on those.
-
Hey
Thanks for solution, This is working for me but it takes long time (41 sec to insert into temp table on 3Gb, 3 GHZ machine, and then join query...) can we have other optimized solution for the same?
I was thinking on
ALTER TABLE <T_NAME > ADD ROWNUM BIGINT NOT NULL DEFAULT IDENTITY (1,1)
but this makes rownum column property as identity, i want to avoid that.
Regards
Shrikant Kulkarni -
Not sure whether this will perform any better but if you create two columns (1 is the identity) and then the identity column
DROP TABLE dbo.test
GO
CREATE TABLE dbo.test (id bigint not null)
GO
ALTER TABLE dbo.test
ADD ROWNUMIDENT BIGINT NOT NULL IDENTITY(1,1),
ROWNUM BIGINT NOT NULL DEFAULT (0)
GO
UPDATE dbo.test
SET ROWNUM = ROWNUMIDENT
GO
ALTER TABLE dbo.test
DROP COLUMN ROWNUMIDENT
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply