Write out to text file using T-SQL

Question

I am creating a basic data transfer task using TSQL where I am retrieving certain records from one database that are more recent than a given datetime value, and loading them into another database. This will happen periodically throughout the day.

It's such a small task that SSIS seems like overkill - I want to just use a scheduled task which runs a .sql file.

Where I need guidance is that I need to persist the datetime from the last run of this task, then use this to filter the records next time the task runs. My initial thought is to just store the datetime in a text file, and update (overwrite) it as part of the task each time it runs.

I can read the file in without problems using T-SQL, but writing back out has got me stuck. I've seen plenty of examples which make use of a dynamically-built bcp command, which is then executed using xp_cmdshell. Trouble is, security on the server I'm deploying to precludes the use of xp_cmdshell.

So, my question is, are there other ways to simply write a datetime value to a file using TSQL, or should I be thinking about a different approach?

EDIT: happy to be corrected about SSIS being "overkill"...

Answer 1

You can build a small app or cmd file that wraps the start of your SQL script from the scheduler. In that app you can store the date and time in a file for you to read from the SQL script the next time it is loaded.

I am sure this can be done better but just to show you the idea, here is the content of a command file that you could use. Read the content of datetime.txt from YourScript.sql

sqlcmd YourScript.sql
sqlcmd -Q"select getdate()" -o datetime.txt

Answer 2

Why use a text file. If you're using TSQL and reading data from a table, keep it consistent and write to the same database. E.g. create a table to store the last query time.