Run bcp as Stored Procedure




Create Proc [dbo].[Sp__Use_bcp]
(
  @databaseName     varchar(30)='test1'
 ,@tableName     varchar(30)='table1'
 ,@filePath   varchar(80)='c:\temp\thisfile.txt'

 )
                                      
as
Set NoCount On
declare @cmd varchar(2000)
Begin
  --pipe delimited
      set @cmd = 'bcp.exe ' +  @databaseName + '.dbo.' + @tableName +  ' out '  +   @filePath + ' -c -t\^| -T -S'+ @@servername
   
      exec xp_cmdShell @cmd
 
End






ALTER Proc [dbo].[Sp__Use_bcp]
(
  @databaseName     varchar(30)='test1'
 ,@tableName     varchar(30)='table1'
 ,@filePath   varchar(80)='c:\temp\thisfile.txt'

 )
                                      
as
Set NoCount On
declare @cmd varchar(2000)
Begin
  --pipe delimited

DECLARE @sql AS VARCHAR(4000)

;with mycte as (select TABLE_CATALOG, table_name, COLUMN_NAME , [ORDINAL_POSITION] from [INFORMATION_SCHEMA].[COLUMNS]
where table_name=@tableName and TABLE_CATALOG=@databaseName)
 
SELECT @sql='" Select ' + stuff( (SELECT ', '+ Quotename(COLUMN_NAME,'''') +' as '+ COLUMN_NAME
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
+  ' UNION ALL Select ' + stuff( (SELECT ', CAST('+ Quotename(COLUMN_NAME,']') +' as varchar(500) ) as '+  Quotename(COLUMN_NAME,']')
FROM mycte m2
WHERE m2.table_name = m1.table_name and m1.TABLE_CATALOG=m2.TABLE_CATALOG
Order by  [ORDINAL_POSITION]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') 
 + '  FROM ' + @databaseName + '..'+ @tableName  + ' " '
FROM mycte m1


      set @cmd = 'bcp.exe ' +  @sql +  ' queryout '  +   @filePath + ' -c -t\^| -T -S'+ @@servername
   
      exec xp_cmdShell @cmd
 
  End


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s