I have a sql script that truncates the table and insert into database from a linked server.Since the server is SQL express I will need to use batch jobs to schedule the jobs. Below is a snippet of the batch job that calls the sql script

TRUNCATE TABLE [TABLE1].[dbo].[TableData]

WAITFOR DELAY '000:00:01'

INSERT INTO [TABLE1].[dbo].[TableData] SELECT * FROM [SourceServer].[SourceDatabase].[dbo].[VW_View]

The batch jobs works fine if I use the below code but since osql is deprecated I will need to use sqlcmd which is runs in a loop and never stops.

osql -S myserver -E -i "c:\test.sql" -o "c:\output.txt" --- Runs fine

sqlcmd -S myserver -E -i "c:\test.sql" ----- Does not work and goes to a loop that does not end.

I will appreciate if someone can provide me the correct syntax for the sqlcmd.

  • Note sure if it would help here but make a habit of specifying the -I SQLCMD argument for SET QUOTED_IDENTIFIER ON.
    – Dan Guzman
    Commented Feb 14 at 12:29
  • Why WAITFOR DELAY? What do you mean a loop...are you continually truncating and reloading the table at a frequent rate?
    – J.D.
    Commented Feb 14 at 13:18
  • Yes truncating the whole table and reloading every 6 hours and it takes aroud 15 seconds Commented Apr 9 at 19:33


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.