TSQL command line execution (sqlcmd)

The Microsoft SQL Server offers pretty good command line capability which can be used in variety of cases.

Recently when I was consulting a client, there was a requirement to move data between two servers. Obvious way was to use any ETL tool but they were not ready for it. Also client was not ready even for an linked server. So a textbook wayout was finalized to dump data into file, read the file into staging table and then load into main table.

The last part was carried out using the windows scheduler with batch script using sqlcmd as below

sqlcmd -S <servername> -U <usedID> -P <password> -Q <query>

The -Q will exit the command prompt directly.

The queries can also be read from script file and outputted to file as below

sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

					
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s