" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
|
I recently had a requirement to purge older data from one of the database. Well yes, a single Delete query can do that easily.
But we needed first to export those old data to CSV file, and then purge them from different tables, so incase needed we can import them back. There are many ways to do this, and one of them is by using SQLCMD Utility.
Suppose we want to export the result set of below query :
SELECT WeatherID, WeatherCondition, Temp, Humidity, WindCondition, LocalTime
FROM WeatherLog
WHERE LocalTime < DATEADD(MONTH, -6, GETDATE())
|
|
The command is like as given below :
SQLCMD -S
Here the switches means :
-S = Server name
-d = Database name
-U = login id/username
-P = password
-Q = Sql query
-s = column separator (here it is ‘comma’ for csv format)
-o = output file
Note : There are many switches available, and can be applied as per the need.http://msdn.microsoft.com/en-IN/library/ms162773.aspx
Ex :
SQLCMD –S mypc
\SQLSERVER
–d TestDB –U sa –P hmm –Q ”SELECT WeatherID, WeatherCondition, Temp, Humidity, WindCondition, LocalTime FROM WeatherLog WHERE LocalTime < DATEADD(MONTH, -6, GETDATE())” –s “,” –o “D:\demo.csv”
You can run it through various means, by command prompt, query window of SSMS, or from a batch file.
There are many other uses of SQLCMD utility, like we can have backup of database by this method. Add that SQLCMD to a batch file, and schedule it to run
|