CODEWITHSUNDEEP
sql-server-2008
sahar
sahar

Reputation: 145

How to create text file in sql server

I have a problem with sql server. I want to run a command in sql server for 691 times,(for each VID).

But the values of VID column are not Sequential and Ascending. so how can I run this command.

My means that I want to run this command for each VID, that the number of its value is 691, and the min value is 11, and the max value is 7668. And then I want to save results for each VID in text file.

The command is:

declare @vid integer

DECLARE @FileName varchar(8000)

declare @bcpCommand varchar(8000)

set @vid = 1

while (@vid < 692)

begin

SET @FileName = 'd:\re'+ CONVERT(char(8),@vid)+'.txt'



SET @bcpCommand =  'bcp "select ak_from,ak_to,w from [socialdb].[dbo].final where ???? "  queryout "'+ @FileName + '" -C -T -c -S SONY-VAIO\SQLEXPRESS1'

EXEC master..xp_cmdshell @bcpCommand



set @vid = (@vid + 1)

end

I want to do this with sql server 2008. The first records is one of my table records.

I should classification records according to vid, and each record that has a same vid must be in a seprat txt file.

For saving in text file I use bcp. and I don’t have problem with that. My problem is with the Query in sql server that do this. As I said, the first records are saved in table that its name is final. How to write the query for this?

Upvotes: 1

Views: 37400

Answers (1)

kmoormann
kmoormann

Reputation: 180

Writing the query for this necessitates that you have the data in some queryable format (e.g. a table in your DB). Once you have that you can easily write a query that SELECTs just the columns you are looking for (ak_from, ak_to, w) and places the result into a text file via bcp (see: Using bcp utility to export SQL queries to a text file and http://msdn.microsoft.com/en-us/library/ms162802.aspx).

Presuming you'll have more than just 5 records and 3 VIDs you could write the bcp within a cursor so that you can loop and create multiple text files based on your conditions.

For the sake of example, the code below simply puts the 5 records into a temp table, and then outputs each of the three text files.

CREATE TABLE ##tbl
(
    Vid INT NOT NULL
    ,ak_from INT NOT NULL
    ,ak_to INT NOT NULL
    ,w INT NOT NULL
)

INSERT INTO ##tbl
SELECT 11, 164885, 164885, 24
UNION SELECT 11, 164885, 431072, 3
UNION SELECT 51, 731754, 690695, 2
UNION SELECT 51, 204086, 316310, 2
UNION SELECT 54, 818522, 501263, 1

DECLARE @vid VARCHAR(2)
DECLARE @querytextNoVid VARCHAR(100)
DECLARE @querytext VARCHAR(100)
DECLARE @filelocation VARCHAR(100)
DECLARE @cmd VARCHAR(255)

DECLARE vid_cursor CURSOR FOR SELECT DISTINCT vid FROM ##tbl
OPEN vid_cursor
FETCH NEXT FROM vid_cursor INTO @vid


WHILE @@FETCH_STATUS = 0
BEGIN

    SET @querytext = '"SELECT ak_from, ak_to, w FROM ##tbl WHERE vid = ' + @vid + '"'
    SET @filelocation = '"c:\out_vid' + @vid + '.dat"'
    SET @cmd = 'bcp ' + @querytext + ' queryout ' + @filelocation + ' -T -c'
    EXEC master..XP_CMDSHELL @cmd

    FETCH NEXT FROM vid_cursor INTO @vid
END
CLOSE vid_cursor
DEALLOCATE vid_cursor

DROP TABLE ##tbl

You'll need to ensure xp_cmdshell is enabled via sp_configure (see: Enable 'xp_cmdshell' SQL Server)

Upvotes: 4

Related Questions