Insert values in alter Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert values in alter Column

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2008-07-22 : 06:01:38

CREATE TABLE [scratch] (
[iId] bigint IDENTITY(1, 1) NOT NULL,
[vScr] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vScrN] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vAt_id] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT NULL NULL,
[vPackage] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT NULL NULL
)
ON [PRIMARY]

I have altered a differen column as:

alter table scratch
add
serial bigint

suppose I have 10 records then Serial will having values as:

query= select * from scratch order by iid

iid vscr vscrn vat_id vPackage Serial

1 4542114014 8964732401 U100000000 Package1 1
2 3297368858 4438646487 U100000001 Package1 2
3 2566298230 2352860025 U100000002 Package1 3
4 2060258458 5429418063 U100000003 Package1 4
5 9234513634 9937323228 U100000004 Package1 5
6 5382504084 9967740849 U100000005 Package1 6
7 4229236881 2277864448 U100000006 Package1 7
8 3025005015 3634655647 U100000007 Package1 8
9 6139427616 6585152793 U100000008 Package1 9
10 1154718548 2167371874 U100000009 Package1 10

please help me to fill values in serial

Ved Prakash Jha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 06:07:37

[code]
update s
set serial = iid
from scratch s
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vedjha
Posting Yak Master

228 Posts

Posted - 2008-07-22 : 06:27:08

but values may be as

1 4542114014 8964732401 U100000000 Package1 1
2 3297368858 4438646487 U100000001 Package1 2
3 2566298230 2352860025 U100000002 Package1 3
21 2060258458 5429418063 U100000003 Package1 4
5 9234513634 9937323228 U100000004 Package1 5
11 5382504084 9967740849 U100000005 Package1 6
71 4229236881 2277864448 U100000006 Package1 7
81 3025005015 3634655647 U100000007 Package1 8
9 6139427616 6585152793 U100000008 Package1 9
10 1154718548 2167371874 U100000009 Package1 10

iid values not in ascending it mayy be any thing but serial must be in ascending

Ved Prakash Jha

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-22 : 06:30:41

With SQL 2005 you can do this...

; with a as (select *, row_number() over (order by iId) row from [scratch])
update a set serial = row

If the order doesn't matter, you can do this...

declare @i int; set @i = 0;
update [scratch] set @i = @i + 1, serial = @i

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

vedjha
Posting Yak Master

228 Posts

Posted - 2008-07-22 : 08:35:27

Thanks Ryan Randall

Ved Prakash Jha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources