Creating a destination file for exportation of MS Sql Server data

pts.
Tags:
Development
SQL
I wish to create a csv file and import data into it, on a regular basis. I wish for the TSQL statement or DTS to create a new and uniquely identifiablecsv file(with specific text)each time the process is run. Anytime I have done something similar I have have to create a csv file with a static file name labeled 'The destination file'. I need to create a new file each time and the file name needs to be set using the statement. Thanks, Stuart

Answer Wiki

Thanks. We'll let you know when a new response is added.

U suffix the current datetime along with the filename. Moreover i have given u a sample SP along with this (importing from csv). u reverse the tablenames in the insert query and if u change the real data table. then the file with the existing data will be created at the location specified. If u r not able to cope with the solution then repel me. i will give you the exact procedure.

Create procedure usp_ImportMultipleFiles @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query =’master.dbo.xp_cmdshell “dir ‘+@filepath+@pattern +’ /b”‘
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
–print @max1
–print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query =’BULK INSERT ‘+ @Tablename + ‘ FROM “‘+ @Filepath+@Filename+’”
WITH ( FIELDTERMINATOR = “,”,ROWTERMINATOR = “n”)’
–print @query
exec (@query)
insert into logtable (query) select @query
end

drop table #y

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JasonC
    Hi Stuart Try something similar to this bcp example. DECLARE @acquirer_company_name VARCHAR(100), @target_company_name VARCHAR(100), @output_file_name VARCHAR(100), @server_name VARCHAR(50), @user_name VARCHAR(20), @password VARCHAR(10), @format_file VARCHAR(100), @command VARCHAR(800) SELECT @output_file_name = 'ServerNameshareddir1dir2Test_File.csv', @server_name = 'NameOfServer', @user_name = 'whatever_user_name', @password = 'whatever_password', @format_file = 'ServerNameshareddir1dir2Test_Format_File.fmt' SET @acquirer_company_name = '%Company1%' SET @target_company_name = '%Company2%' SET @output_file_name = @output_file_name + GETDATE() SELECT @command = 'bcp "EXEC mna_dist.dbo.stored_proc_name ''' + @acquirer_company_name + ''', ''' + @target_company_name + '''" queryout "' + @output_file_name + '" -f"' + @format_file + '" -S' + @server_name + ' -U' + @user_name + ' -P' + @password + ' ' EXEC master..xp_cmdshell @command
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following