Sql-server – How to read and increment a number in text file using TSQL

sql serversql-server-2008t-sql

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

Best Answer

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.

Related Solutions

Sql-server – Iterating over the output of a SQL query with PowerShell

PowerShell offers the ability to do things in many different ways, so this is just the method I would do in solving the problem at hand, based on the information provided.

SMO offers the ability to check the table class to more efficiently find the database in question that contain the table and column specified; instead of trying to use INFORMATION schema views that some folks have a case against ever using them.

I would break this up into sections that will make it more easy to debug. I am assuming you are not worried what UNC path is tied to which server, just making sure the UNC path exist and the file has been moved. So I would first pull all the UNC paths from any database I find the table and column exist.

#Load assembly
[void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$serverList = 'server1','server2','server3\instance1','server3\instance2'

$searchTableName = 'File'
$searchColumnName = 'Location'

$uncPaths =
foreach ($s in $serverList)
{
    $srv = new-object microsoft.sqlserver.management.smo.server $s

    # The first "where" filters for the database with the specified table name
    # The second "where" filters for the column to exist within the table
    # The "-ExpandProperty Parent" is to go backwards in order to output just 
    #   the database name that contains the table and column
    $dbList = $srv.Databases | select -ExpandProperty Tables | 
        where {$_.name -match $searchTableName} | 
        select -ExpandProperty Columns | where {$_.name -match $searchColumnName} | 
        select -ExpandProperty Parent | select -ExpandProperty Parent |
        select -ExpandProperty name -unique

    # If $dbList count is greater than zero means it found a database
    if ($dbList.Count -gt 0)
    {
        #now we need to pull the location paths
        foreach ($d in $dbList)
        {
            $qry = "SELECT [Location] FROM $($searchTableName)"

            # The "-ExpandProperty" can also aid in outputting an object as just a string value
            Invoke-Sqlcmd -ServerInstance $s -Database $d -query $qry | select -ExpandProperty Location
        }
    }
}

# this variable should now contain all the UNC paths you need
$uncPaths

Now in regards to changing the path to the old location I would do something like this when I am iterating through the $uncPaths variable:

foreach ($f in $uncPaths)
{
    $oldPath = $f.Replace("Current","Old")
}

Sql-server – how to set “result to text” with a tsql statement in sp

No, how the results are displayed (if at all) is the job of the client application.

Best Answer

Related Solutions

Sql-server – Iterating over the output of a SQL query with PowerShell

Sql-server – how to set “result to text” with a tsql statement in sp

Related Question