0

I have a text file C:\abc.txt with a number 5 in the first line. I need to increment it into 6.

Please help me with the T_SQL syntax.

I have tried the following query to read the value but not able to update it.

DROP TABLE #UserLocation

CREATE TABLE #UserLocation(UserId numeric(3,0))     

BULK INSERT #UserLocation
    FROM 'C:\ABC\ABC.txt'  
    -- Path to the file.If this file is on server,be sure this file exists on the server.
    WITH 
    (
        ROWTERMINATOR ='\n', 
        --  New Line Feed (\n) automatically adds Carrige Return (\r)
        FIELDTERMINATOR = '\t', 
        --delimiter 
        FIRSTROW = 1
    )   

PRINT 'File data copied to Temp table'      

SELECT * FROM #UserLocation 
3
  • This really isn't what SQL Server was designed for. Are you simply trying to import, update the number and then export the file again? Jan 7 '16 at 9:32
  • Do you need to use the value in the text file within SQL Server or merely increment it? Jan 7 '16 at 13:13
  • just i need to increment the value in the text file. No other use in SQL Server. Jan 8 '16 at 8:16
4

The simplest way to read and increment a numeric value in a text file would be to not do it in SQL Server, though you can still use SQL Server to cause it to happen.

You can create a CMD script as follows that accepts a single input parameter for the file name and then increments the value inside of that file by 1. If the file name does not exist, it will be created with an initial value of 0.

Just create a new text file in Windows Explorer and replace the entire name, including the extension, with: IncrementValue.cmd.

Then edit the file and paste in the following:

@ECHO OFF

SETLOCAL ENABLEDELAYEDEXPANSION

IF NOT EXIST %1 ECHO 0 > %1

FOR /F %%A IN ('TYPE %1') DO (
    SET /A NewTempValue=%%A+1
    ECHO !NewTempValue! > %1
)

REM Uncomment the following line to get the value back in xp_cmdshell
REM ECHO !NewTempValue!

Then just call it in SQL Server, either from a Command-step in a SQL Server Agent job, or via xp_cmdshell like so:

-- remove the ", NO_OUTPUT" if you need to get the new value back
EXEC xp_cmdshell N'IncrementValue.cmd MyFile.txt', NO_OUTPUT;

Or, this same operation could be done in a PowerShell script.

1
SET NOCOUNT ON

CREATE TABLE ##UserLocation (UserId NUMERIC(3, 0))

DECLARE @CMD NVARCHAR(255)

BULK INSERT ##UserLocation
FROM 'C:\ABC\ABC.txt' WITH (
        FirstRow = 1
        ,FieldTerminator = '''+@fieldsep+'''
        ,RowTerminator = '''+@recordsep+'''
        );

SET @CMD = 'BCP "select CONVERT(VARCHAR(10),UserId+1) from ##UserLocation "     QUERYOUT "C:\ABC\ABC.txt" -T -c -t,'

EXEC xp_cmdshell @CMD
    ,NO_OUTPUT

DROP TABLE ##UserLocation
GO

Using ##temptable gives results.

0

since you are using temp table you can use bulk editing your commands to make it like this

DROP TABLE #UserLocation;
create table #UserLocation (UserId numeric(3,0))
go
bulk insert #UserLocation
        from 'e:\test.txt'
        with (FirstRow = 1, FieldTerminator = '''+@fieldsep+''', RowTerminator = '''+@recordsep+''');
select * from #UserLocation;

Regarding increment the value use:

select UserId+1 from #UserLocation 

and if you want to export it to text file with one step you can use:

EXEC xp_cmdshell 'bcp "select UserId+1 from #UserLocation;" queryout "e:\test.txt" -T -c -t,'

usful links:

  1. How to enable xp_cmdshell http://blog.sqlauthority.com/2007/04/26/sql-server-enable-xp_cmdshell-using-sp_configure/
  2. How to export file into text file from sql server: https://www.mssqltips.com/sqlservertip/1633/simple-way-to-export-sql-server-data-to-text-files/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.