Insert number sequence in Column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Insert number sequence in Column?

Author

Topic

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-02-08 : 10:46:55

Hi

I want to know if you can insert a number sequence in an 'select into' or 'insert' statement e.g:

SELECT Column1, Column2, Column3,
INTO Table2
FROM Table1

However a table2 will have and extra Column called 'Column4' which I want to populate with a number everytime a record is inserted in Table2.

Does this make sense?

Thanks in advance

Gopher

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 10:56:51

Here ya go. Create a # or @ table (# is a physical table, @ is one in memory) with an identity column. Insert into it from table 1 and then insert into table 2 from your temp table.


Declare @Ident Table 
(
	Column1 varchar(50), 
	Column2 varchar(50), 
	Column3 int, 
	--Here is where you get your incrimenting number
	Column4 int identity(1, 1)
)

--Use this to see results
Insert Into @Ident
Select 
	SO.name As TableName,
	SC.name As ColumnName,
	SC.colorder As ColumnOrder
From sysobjects SO
Inner Join syscolumns SC On SO.id = SC.id
Where SO.xtype = 'U'
Order By SC.colorder

--Here is what it looks like.  Note Column4
Select * From @Ident

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-02-08 : 11:05:14

Cool!!!
Thanks Qualis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 12:40:06

or use the ROW_NUMBER() function to generate the unique number:-

SELECT ROW_NUMBER() OVER (ORDER BY Column1) as UniqueNumber,
Column1, Column2, Column3, 
INTO Table2
FROM Table1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-09 : 01:37:42

and if the table2 already exists then

INSERT INTO table2(col1,col2,col3,col4)
SELECT Column1, Column2, Column3, ROW_NUMBER() OVER (ORDER BY Column1) INTO Table2
FROM Table1

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-02-10 : 12:10:25

I guess I don't understand why he doesn't just make col4 and Identity on the target table...

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources