Here is one way that can be easily made into a procedure.
--
-- Working example for @vcTable.
--
USE pubs
GO
DECLARE @vcTable VARCHAR(128)
SET @vcTable = 'authors'
DECLARE @vcColumn VARCHAR(8000),
@vcINSERT VARCHAR(8000)
DECLARE
@vcSQL VARCHAR(8000),
@vcFormatting VARCHAR(2),
@bDatesAsText bit
SELECT @vcFormatting = CHAR(10)+ CHAR(9),
@bDatesAsText = 1
SELECT @vcColumn = COALESCE( @vcColumn + ' VARCHAR(128),', 'CREATE TABLE ##x (') + COLUMN_NAME,
@vcINSERT = COALESCE(@vcINSERT + ''',', 'INSERT ##x VALUES (') + '''' + COLUMN_NAME,
@vcSQL =
COALESCE(@vcSQL + ', ' + @vcFormatting , 'SELECT ' ) +
CASE
WHEN @bDatesAsText = 1
AND DATA_TYPE IN ('datetime','smalldatetime') THEN
'char(34) + convert(varchar(23), ' + QUOTENAME(COLUMN_NAME) + ', 121) + char(34)'
WHEN DATA_TYPE IN ('varchar','nvarchar','char','nchar','text','ntext') THEN
'char(34) + ' + QUOTENAME(COLUMN_NAME) + '+ char(34)'
ELSE
QUOTENAME(COLUMN_NAME)
END + ' AS ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @vcTable
ORDER BY
ORDINAL_POSITION
SELECT @vcColumn = @vcColumn + ' VARCHAR(8000))',
@vcINSERT = @vcINSERT + ''')',
@vcSQL = @vcSQL + '
FROM ' + DB_NAME() + '.dbo.' + @vcTable
PRINT @vcColumn
PRINT @vcINSERT
-- This must be in one statement or the optimizer thinks the table doesn't exist.
EXEC (@vcColumn + ';' + @vcINSERT)
-- add data here ...
PRINT @vcSQL
EXEC ('INSERT ##x ' + @vcSQL)
SELECT *
FROM ##x
DECLARE @vcCmd VARCHAR(8000),
@vcOutFile VARCHAR(4000),
@vcFieldParameter VARCHAR(2)
SELECT @vcOutFile = 'E:\MSSQL\REPORTS\test.bcp',
@vcFieldParameter = ',',
@vcCmd = 'bcp "SELECT * FROM ##x" queryout "' + @vcOutFile +
'" -S' + @@SERVERNAME + ' -T -t ' + @vcFieldParameter + ' -c '
PRINT @vcCmd
EXEC master.dbo.xp_cmdshell @vcCmd
-- very important
DROP TABLE ##x
--
--
--