Create .txt file from SQL statement
I am trying to make a SQL statement to create a file .txt
for each ID added to the database, but that doesn't work. I am trying this:
DECLARE @FileName varchar(50), @bcpCommand varchar(2000)
SET @FileName = REPLACE('G:\'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM dbo.dbTest WHERE (DATEADD(MINUTE,-1420, GETDATE())) < [date];" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c'
PRINT @bcpCommand
and of course I need this result saved in G: /, but that doesn't work for me. My db name is testDB
+3
source to share
1 answer
Did you mean you want to create a bcp file for each database? Just go through all databases and make sure you provide the database name parameter for your bcp statement so that it knows which database the query will run against. Another thing you need to fix is the filename. You can change it below to add the db name to the output file so you don't overwrite every result.
DECLARE @FileName varchar(50), @bcpCommand varchar(2000)
declare @x int = 5
declare @y int
declare @dbname varchar(100)
select @y = max(database_id) from sys.databases
while @x <= @y
begin
select @dbname = name from sys.databases where database_id = @x
if @dbname is not null
begin
SET @FileName = REPLACE('G:\'+CONVERT(char(8),GETDATE(),1) +'.txt','/','-')
SET @bcpCommand = 'bcp "SELECT * FROM dbo.dbTest WHERE (DATEADD(MINUTE,-1420, GETDATE())) < [date];" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c -d"' + @dbname + '"'
PRINT @bcpCommand
end
set @x = @x + 1
end
Examples of results:
bcp "SELECT * FROM dbo.dbTest WHERE (DATEADD(MINUTE,-1420, GETDATE())) < [date];" queryout "G:\05-16-15.txt" -U garth -P pw -c -d"db1"
bcp "SELECT * FROM dbo.dbTest WHERE (DATEADD(MINUTE,-1420, GETDATE())) < [date];" queryout "G:\05-16-15.txt" -U garth -P pw -c -d"db2"
bcp "SELECT * FROM dbo.dbTest WHERE (DATEADD(MINUTE,-1420, GETDATE())) < [date];" queryout "G:\05-16-15.txt" -U garth -P pw -c -d"db3"
bcp "SELECT * FROM dbo.dbTest WHERE (DATEADD(MINUTE,-1420, GETDATE())) < [date];" queryout "G:\05-16-15.txt" -U garth -P pw -c -d"db4"
0
source to share