Select into using identity - 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

Select into using identity

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-09-18 : 04:04:24

Hi guys,

I've a question about the 'select into' clause when using an identity column with an order by clause.

I've attached a sample of code at the bottom.

I've recently read an article (http://support.microsoft.com/kb/273586) where it states that you can't guarantee the order of the identity columns using this statement, however I'm not sure whether this applies to 2005 or not.

Using this statement, I always get the correct order.
----------
begin

CREATE TABLE #x (FileName varchar(50))

INSERT INTO #x(FileName) SELECT 'E'
INSERT INTO #x(FileName) SELECT 'A'
INSERT INTO #x(FileName) SELECT 'C'
INSERT INTO #x(FileName) SELECT 'B'
INSERT INTO #x(FileName) SELECT 'D'

SELECT * FROM #x

SELECT identity(int,1,1) AS ID, FileName
INTO #y
FROM #x
ORDER BY FileName

SELECT * FROM #y

DROP TABLE #x
DROP TABLE #y

end
----------

Can anyone tell me if it is safe to use rely on the ordering of this statement in 2005, is there an article which discusses this ?

Sean

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-18 : 04:12:24

In SQL Server 2005, you dont need it. You can make use of ROW_NUMBER() function

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 04:34:21

SELECT FileName, ROW_NUMBER() OVER (ORDER BY FileName) AS ID
FROM #x

With ROW_NUMBER() function, you can also partition the result.

E 12°55'05.25"
N 56°04'39.16"

Sean_B
Posting Yak Master

111 Posts

Posted - 2007-09-18 : 04:39:06

Thanks Madhivan,

I've done it like this

SELECT ROW_NUMBER() OVER(ORDER BY FileName) AS ID, FileName
INTO #y
FROM #x

Do I need to change any existing queries that use the identity approach ?

Sean

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2007-09-18 : 04:54:26

Hi Sean,

Don't forget to add "ORDER BY FileName" to the above statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 04:58:02

You don't get it, do you?
You do not have to insert into #y table to get your results!

See my post at 09/18/2007 : 04:34:21

E 12°55'05.25"
N 56°04'39.16"

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2007-09-18 : 04:58:57