January 10, 2008 at 4:42 am
I've been asked to provide an export file where rownumbers must be provided. For each new export file the rownumber sequence must start from 1 again...
the simplest way of doing this (that I have thought of) is to insert the contents of my view into an export table. Use the identity column to get my row number... Stuff the contents into my Export file... Delete contents from my Export file before next export and reseed the identity column.
Surely there's is a simpler way of doing this?
January 10, 2008 at 4:57 am
You can use the row_number() function (available on 2005). An example is:
select row_number() over (order by columnname) AS rownumber, * from somerandomtable
just replace the columnname and somerandomtable with some relevant values, like:
select row_number() over (order by object_id) AS rownumber, * from sys.all_objects order by object_id
Regards,
Andras
January 10, 2008 at 5:33 am
thanks for that... should have mentioned that I'm connecting to 2000... (crucial info missing)...
I have found the following though which seems to work
select (select SUM(1) from view1 where na_primary <= vw.na_primary ) as 'Row Number', * from view1 vw
the problem is that this runs very very slowly...
January 10, 2008 at 6:36 am
bleighton (1/10/2008)
thanks for that... should have mentioned that I'm connecting to 2000... (crucial info missing)...I have found the following though which seems to work
select (select SUM(1) from view1 where na_primary <= vw.na_primary ) as 'Row Number', * from view1 vw
the problem is that this runs very very slowly...
If you are on 2000 you could use select into with column identity. So there will be no need to reseed, you can specify the indentity (together with its type, seed and increment) in the select statement.
Example:
select identity(int, 1, 1) as rownumber, * into foo from sysobjects
Regards,
Andras
January 10, 2008 at 7:00 am
nice
January 10, 2008 at 10:11 am
bleighton (1/10/2008)
thanks for that... should have mentioned that I'm connecting to 2000... (crucial info missing)...
If you're using 2000, it's best to post in the SQL 2000 forums. In the 2005 foryums people tend to assume that you're using SQL 2005
I have found the following though which seems to work
select (select SUM(1) from view1 where na_primary <= vw.na_primary ) as 'Row Number', * from view1 vw
the problem is that this runs very very slowly...
And chows memory. That triangular join will kill your server with more than a couple hundred rows. Not top mention the correlated subquery. I'd hate to see the number of rows generated internally for that.
Jeff wrote an article on triangular joins here a short while back. Have a read through that and you'll see why that query is slow.
Andras's query is probably the best on SQL 2000. Just note you're still creating a temp table.
(SELECT ... INTO)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2008 at 10:33 am
>>If you are on 2000 you could use select into with column identity.
That is how I would do it on SQL2K, but if and only if, there is no explicit ORDER BY required.
If the rownumber on the exported records is supposed to capture something about the physical ordering of the data in the records, then use of SELECT INTO ... ORDER BY will not guarantee you the order.
January 10, 2008 at 11:00 am
Thanks for that reference Gail... trianglar/x-joins joins are evil... though I seem to remember using them once to put together some test data.
and point taken about the 2000 forum... I guess next time I'll check the object explorer before assuming.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply