Prev: sp_addlinkedserver -- sql server 2000 -- problem
Next: How clone a view
From: Brian Conner via SQLMonster.com on 12 Jan 2010 09:34
I have a Query that returns a lot of Data and I would like to have it Export
to a Text File with No More than 60,000 records in each file. So if my Query
contains 200,000 records I would like the Routine to break it up into 4 text
files, 60,000 records in 3 files and 20,000 in the fourth. What would be the
code for this? Any help is greatly appreciated.

--
Brian Conner

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: Itzik Ben-Gan on 12 Jan 2010 10:09
If you need to address which row goes to which file as part of the querying
logic (as opposed to controlling this in the export process) you can use the
ROW_NUMBER function. If ordering matters, you can use something like:

SELECT *,
(ROW_NUMBER() OVER(ORDER BY <your_ordering>) - 1) / 60000 + 1 AS filenum
FROM YourTable
ORDER BY <your_ordering>;

If ordering doesn't matter, use:

SELECT *,
(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1) / 60000 + 1 AS filenum
FROM YourTable;

--
Itzik Ben-Gan | SQL Server MVP | www.SolidQ.com | www.InsideTSQL.com


"Brian Conner via SQLMonster.com" <u47161(a)uwe> wrote in message
news:a200e8cd3cced(a)uwe...
> I have a Query that returns a lot of Data and I would like to have it
> Export
> to a Text File with No More than 60,000 records in each file. So if my
> Query
> contains 200,000 records I would like the Routine to break it up into 4
> text
> files, 60,000 records in 3 files and 20,000 in the fourth. What would be
> the
> code for this? Any help is greatly appreciated.
>
> --
> Brian Conner
>
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1
>
 | 
Pages: 1
Prev: sp_addlinkedserver -- sql server 2000 -- problem
Next: How clone a view