Numbering - 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

Numbering

Author

Topic

Minami
Starting Member

3 Posts

Posted - 2005-10-05 : 12:23:06

Hi guys,

I have a table with 3 col like below

col1 col2 col3
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67

none of them are unique, some are null and there are about 800,000 rows

I want to insert a col before 1 or after 3 and have numbering from 1 to the last row. How can I do that?

Thank you
Mina

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-05 : 12:38:32

Can you tell me what good it'll do you?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(LastName varchar(50), FirstName varchar(50), Address varchar(50), City varchar(50))
GO

INSERT INTO myTable99(LastName, FirstName, Address, City) 
SELECT 'Nilsen', 'Fred', 'Kirkegt', '56 Stavanger' UNION ALL 
SELECT 'Rasmussen', 'Nina', 'Storgt', '67'
GO

SELECT * FROM myTable99
GO
 
ALTER TABLE myTable99 ADD RowNum int IDENTITY(1,1)
GO

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Minami
Starting Member

3 Posts

Posted - 2005-10-05 : 13:16:58

Hi Brett,

Thank you for your help. Really appreciate your promptly reply.

This is what happen. I have 2 tables. The one above is the first table and I have another table
col1 col2 col3 col4
firstname last name sex country

I have to put them all together into 1 table. I was thinking about indexing them and cycle through one table looking for the info on the other and update them.

Note some might have the same last name or first name. So I kinda lost. If you could spare sometime to help me would really apprecaite.

Greatest graditude
Mina

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-05 : 14:25:10

Mina, could you help me out some. Please read the link below my sig and post the relavent information as I have outlined there.

Thanks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Minami
Starting Member

3 Posts

Posted - 2005-10-05 : 14:59:14

Sorry Brett,

I will do a better work next time

Mina

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-05 : 15:28:11

Don't be sorry.

Do you understand what I'm asking from you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources