CODEWITHSUNDEEP
sqlsql-servercsvstored-proceduressql-server-2005
Edward Heathcote
Edward Heathcote

Reputation: 3

Unique filename output from an SQL query

I have the following code, but now need to have a unique filename (date at the end) on the sales data file

USE [KevinMayhewLive]
GO
/****** Object:  StoredProcedure [dbo].[toFileSalesData]    Script Date: 06/19/2017 13:36:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[toFileSalesData]
as

--xp_cmdshell will shell out to the command line to run bcp
--the user account that runs this procedure should have file access to create and write files
--bcp does not append to files, all data will be overwritten each time this procedure runs

EXEC KevinMayhewLive..xp_cmdshell 'bcp "SELECT * FROM KevinMayhewLive.dbo.KM_CUSTOMER_DATA" queryout \\SERVER3\Docs\Emarsys_Sync\Customer_data\CUSTOMER_DATA.csv -c -t, -T -S'

EXEC KevinMayhewLive..xp_cmdshell 'bcp "SELECT * FROM KevinMayhewLive.dbo.KM_SALES_DATA" queryout \\SERVER3\Docs\Emarsys_Sync\Sales_Data\sales_items.csv -c -t, -T -S'

Upvotes: 0

Views: 1433

Answers (1)

alroc
alroc

Reputation: 28194

You don't describe what you mean by a "unique" filename, but if you want to include a timestamp in the filename (which would in most cases result in uniqueness), you'll have to dynamically generate a filename. For example:

EXEC xp_cmdshell 'bcp "SELECT * FROM KevinMayhewLive.dbo.KM_CUSTOMER_DATA" queryout \\SERVER3\Docs\Emarsys_Sync\Customer_data\CUSTOMER_DATA_'+REPLACE(CONVERT(NVARCHAR(40), GETDATE(), 120),':','')+'.csv -c -t, -T -S'

Upvotes: 1

Related Questions