Maximum number of records - 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

Maximum number of records

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-07-20 : 09:39:50

I have been asked to write a script to create a text file with a maximum number of records. Is there a way I can limit how many records are inserted into a txt file using sql script..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 09:47:49

If there was no limit to the number of records, how were you planning to write it? I assume you are trying to export from a database to a text file. If you were planning to use BCP, SSIS etc., in each case, you can limit the number of records in the select query that would retrieve the records (by using TOP N clause, for example).

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 09:48:44

can you limit it in the sql query? something like:

SELECT TOP (10) column1, column2
FROM table

------------------------
PS - Sorry my bad english

divan
Posting Yak Master

153 Posts

Posted - 2012-07-20 : 11:24:38

I appreciate all your responses. And the TOP N clause came to mind but then it is not that I only want the first 5000 records I do want all the records I was wondering if there is a way to have a some kind of IF THEN clause where the first 50000 will go into one file and the next will go into another..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-20 : 11:31:17

Sure, you just have to use a little programming and logic. You could use a WHILE loop to do selects of some "chuck" size (50000 or whatever) and output that to a file until there are no more rows to process.

divan
Posting Yak Master

153 Posts

Posted - 2012-07-20 : 12:05:40

OK I guess I need some help with programming.. Here is the code

SET NOCOUNT ON
DECLARE @x INT
SET @X = 10

WHILE @x > 0
BEGIN

SELECT DISTINCT M.POLICY_NUMBER,M.CLIENT_NUMBER,C.FNAME1,C.LNAME1,E3.DESCRIPTION AS EXPOSURE_TYPE,

FROM MPL_EXPOSURE M
INNER JOIN EDIT_LONG_CODE E ON E.TBNAME = 'MPL_EXPOSURE' AND E.NAME = WHERE M.POLICY_NUMBER NOT LIKE 'Q%'

ORDER BY M.POL_EXP_TYPE,M.EXP_PREM---- M.CLIENT_NUMBER
SET @x= @x -1
END

what this does it list 1027 records 10 times...

PS: I am not SQL programmer that is why I am confused...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-20 : 12:54:27

What version of SQL are you using?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-20 : 12:57:38

You need to add some intelligence to the query to tell it which rows (rather than the same rows) to pick. One way to approach this is to

a) put all the data you want to export into temporary table
b) add a column to indicate a serial number
c) pick the rows to export based on the serial number.

In your case, the code would be something like this:

-- This puts all the data into a temporary table.
-- Fix your query - what should be E.Name? I set it to "Something?"
SELECT
	DISTINCT M.POLICY_NUMBER,
	M.CLIENT_NUMBER,
	C.FNAME1,
	C.LNAME1,
	E3.DESCRIPTION AS EXPOSURE_TYPE
INTO
	#tmp
FROM
	MPL_EXPOSURE M
	INNER JOIN EDIT_LONG_CODE E
		ON  E.TBNAME = 'MPL_EXPOSURE' AND E.NAME = 'Something?'
WHERE
	M.POLICY_NUMBER NOT LIKE 'Q%'

-- Now add a sequence number column to the temp table

ALTER TABLE #tmp ADD SeqNum INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED;

And then run the select statement from the temp table.

DECLARE @ChunkSize INT, @TotalRowsToExport INT;
SET @ChunkSize = 10;
SELECT @TotalRowsToExport = COUNT(*) FROM #tmp;

DECLARE @counter INT
SET @counter = 0;

WHILE @counter < @TotalRowsToExport
BEGIN
    SELECT TOP (@ChunkSize)
		POLICY_NUMBER,CLIENT_NUMBER,FNAME1,LNAME1,EXPOSURE_TYPE
    FROM
    	#tmp
    WHERE
		SeqNum > @counter
    ORDER BY
    	SeqNum;
    SET @counter = @counter+@ChunkSize;
END

The code compiles, but I have not tested it.

divan
Posting Yak Master

153 Posts

Posted - 2012-07-20 : 15:12:44

THanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources