Sequential Number Column starting at known number

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

Sequential Number Column starting at known number

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2011-03-31 : 16:09:01

I have a table with a blank column called AccountGroupID. I need to start at the first row and number from 1081052 to 1082542. Then I need to cast that temporary table into the permanent table.

Does anyone have any suggestions on how to do this?

Thank you,

Mary

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 16:09:43

Check out ROW_NUMBER function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2011-03-31 : 16:25:28

All I want to do is a simple update and I am trying to find an example of ROW_Number function that applies but I'm not getting anywhere:

update dbo.Sheet1$
Set AccountGroupID = ??????????
Where AccountGroupID= ' '

So the result is:

AccountGroupID
1080152
1080153
1080154
etc.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-31 : 16:29:09

DECLARE @c int
SET @c=1081052 - 1
UPDATE dbo.Sheet1$
Set @c = AccountGroupID = @c +1
Where AccountGroupID= ' '

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2011-03-31 : 16:36:33

Thank you Robvolk!!!! That worked very well. One more question. How do I get the temporary table imported into the master table?

Thank you,

Mary

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-31 : 16:42:39

SELECT * INTO newTable FROM oldTable

Or:

EXEC sp_rename 'oldTable', 'newTable', 'OBJECT'

Use the first one if you need to leave the original table in place and make a copy of it.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2011-03-31 : 16:59:59

Insert Into dbo.accountgroup (AccountGroupID, AccountID, GroupCode, BeginningDate,
Permanent_, Note)
Select AccountGroupID, [Account ID], GroupCode, BeginningDate,
Permanent_, Note
From
Sheet1$

*****************************************************************************

select *
From accountgroup
ORDER by AccountGroupID

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2011-03-31 : 17:00:21

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources