Copy multiple files into one with xp_cmdshell with variable path using SQL

Question

I've created some csv files using bcp in Sql Server 2008. Now I need to merge those files into one. It works if I define explicitly the path of the files but if I declare it it tells me that it can not find the files.

declare @variable_path_file1 = "c:\file.csv" declare @variable_path_file2 = "c:\file2.csv"

it works like this:

exec master..xp_cmdshell 'copy /b "c:\file.csv" + "c:\file2.csv" "C:\result.csv"'

But it doesnt work like this:

exec master..xp_cmdshell 'copy /b @variable_path_file1 + @variable_path_file2 "C:\result.csv"'

Nor like this:

 exec master..xp_cmdshell 'copy /b '@variable_path_file1' + '@variable_path_file2' "C:\result.csv"'

Neither like this:

 exec master..xp_cmdshell 'copy /b "'@variable_path_file1'" + "'@variable_path_file2'" "C:\result.csv"'

Can someone help me, please?

Answer 1

Use like below

SET @cmd = 'copy /b "' + @v_Header_path +'" + "'+ @v_Data_path +'" "' + @v_Out_file_path + '"'
exec master..xp_cmdshell @cmd