SQL Server – Add Date/Time to output file of BCP / SQLCMD
You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify the logic to suit your requirement.
Step 1: First let us create a stored procedure which will provide the data to be exported:
CREATE PROCEDURE ExportData
AS
SET NOCOUNT ON
SELECT 'Vishal', 'SqlAndMe'
GO
EXEC dbo.ExportData
GO
Result Set:
—— ——–
Vishal SqlAndMe
I have selected string here to keep things simple. You can specify any query in stored procedure which produces required data.
Step 2: Now, we will write the T-SQL code to export data returned from this stored procedure. Here we will use SQLCMD (you can also use BCP) to export data. We will execute SQLCMD using xp_cmdshell extended stored procedure.
DECLARE @sqlCommand VARCHAR(1000)
DECLARE @filePath VARCHAR(100)
DECLARE @fileName VARCHAR(100)
SET @filePath = 'C:\Temp\'
SET @fileName = 'MyFile_' +
+ CONVERT(VARCHAR, GETDATE(), 112) + '_' +
CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR) + '_' +
CAST(DATEPART(MINUTE,GETDATE()) AS VARCHAR) + '.txt'
SET @sqlCommand =
'SQLCMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" -o "' +
@filePath + @fileName +
'" -h-1'
–Uncomment if you want to use BCP
–SET @sqlCommand =
— 'bcp "EXEC ExportData" queryout "' +
— @filePath + @fileName +
— ' " -S (local) -T -d SqlAndMe -c'
–PRINT @sqlCommand
EXEC master..xp_cmdshell @sqlCommand
GO
The above code will create the required file as "MyFile_YYYYMMDD_HH_MM.txt".
You can verify the command generated by uncommenting the PRINT statement in the code above. Also, you can uncomment the fourth SET statement in case you want to use BCP command to export the data.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Leave a comment Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Recent Posts
- SQL Server – Different Ways to Check Object Definition
- SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio
- SQL Server – How to get last access/update time for a table
- SQL Server – Displaying line numbers in Query Editor – SSMS
- SQL Server – Difference between @@CONNECTIONS and @@MAX_CONNECTIONS
- SQL Server – Different ways to check Recovery Model of a database
- SQL Server – Calculating elapsed time from DATETIME
- SQL Server – Kill all sessions using database
- SQL Server – Custom sorting in ORDER BY clause
- SQL Server – Script to get Service Account for all local instances
Disclaimer
This is a personal weblog. The opinions expressed here are my own and not of my employer. For accuracy and official references refer to MSDN, Microsoft TechNet, Books Online. I or my employer do not endorse any of the tools / applications / books / concepts mentioned here on my blog. I have simply documented my personal experiences on this blog.
Categories
- Backup & Recovery (11)
- Catalog Views (25)
- Certification (1)
- Common Table Expressions (6)
- Database Mail (1)
- Management Studio (38)
- Management Views and Functions (11)
- Partitioning (3)
- Service Pack Releases (2)
- SQL Agent (7)
- SQL Bugs (2)
- SQL Configuration (30)
- SQLServer (164)
- SQLServer 2005 (119)
- SQLServer 2008 (122)
- SQLServer 2008 R2 (123)
- SQLServer 2012 (63)
- Uncategorized (1)
- Undocumented Functions (21)
- Working With Data (14)
Hi Vishal, this post has been very helpful. I was wondering, if we can use SQLCMD to export table as pdf?