copy data to csv files - 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

SQL Server 2000 Forums

Transact-SQL (2000)

copy data to csv files

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-08-14 : 10:23:14

Hi,
I am using a script to import a csv file into SQL server 2000 table then export the data from the table into multiple csv files depending on the values..

my data looks like :

pro_no forname surname
1 bill hicks
2 bill conelly
1 lee evans
3 bill bailey

I need to output the data to files where all pro_no 1;s are together, all 2;s together etc.

I am currently using :

DECLARE @id int,
@intRowCount int,
@FileName varchar(100),
@Formatfile varchar(100),
@bcpCommand varchar(2000)

SET @intRowCount = 1 -- Force first iteration
SET @id = -1 -- Impossible minimum value

WHILE @intRowCount > 0
BEGIN
SELECT @ID = MIN(Prov_No) from EY_Test WHERE Prov_No > @ID
SET @intRowCount = @@ROWCOUNT

IF @intRowCount = 1
BEGIN
---run bcp command

SELECT @ID = @ID + 1

END

but this keeps running forever.. how can I modify whatI have to only loop through select distinct pro_no from temp_table ?

regards,
Jamie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 06:14:12

That because when no more id is found (or a gap in the id sequence is found), @intRowCount becomes Zero and never increments any more. Use a code like this instead.

DECLARE	@CurrentID INT,
	@MaxID INT,
	@FileName VARCHAR(100),
	@FormatFile VARCHAR(100),
	@bcpCommand VARCHAR(2000)

SELECT	@CurrentID = MIN(Prov_No),
	@MaxID = MAX(Prov_No)
FROM	EY_Test

WHILE @CurrentID <= @MaxID
	BEGIN
		---run bcp command

		SELECT	@CurrentID = MIN(Prov_No)
		FROM	EY_Test
		WHERE	Prov_No > @CurrentID
	END

It starts with the minimum id and ends with maximum id and in between, it never goes into a gap or missing id.

Peter Larsson
Helsingborg, Sweden

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-08-15 : 06:56:46

Cheers Peter !
you make it look so easy !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources