1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
|
In Tsql
--- xp_cmdshell Enable
EXEC sp_configure 'show advanced options' , 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell' , 1
GO
-- Prend en compte la modification faite
RECONFIGURE
GO
-------The TSQL script ;
declare @TABLE_CATALOG varchar(8000)
declare @TABLE_SCHEMA varchar(8000)
declare @TABLE_NAME varchar(8000)
declare @Column varchar(8000)
declare @Type varchar(8000)
declare @strSQL varchar(8000)
declare @Qualifier varchar(8000)
declare @FileName varchar(8000)
declare @strWhere varchar(8000)
set @TABLE_CATALOG = 'CdbProdV2' --Database
set @TABLE_SCHEMA = 'Bruprod' -- Owner ie: dbo
set @TABLE_NAME = 'vw_west_A' -- Table nane
set @FileName = 'c:\bcp\test.csv' --Path+FileNAme
set @strWhere = 'where fc_cy=''33''' --@strWhere='' or @strWhere=' ... and ... and ...'
set @Qualifier ='"' --Text Qualifier only for the text format
set @Column = ''
Declare cur_Column cursor
for
select Column_name,Data_type from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG =@TABLE_CATALOG
and TABLE_SCHEMA =@TABLE_SCHEMA
and TABLE_NAME=@TABLE_NAME
open cur_Column
fetch cur_Column into @Column,@Type
set @strSQL =''
while (@@FETCH_STATUS=0)
begin
set @strSQL = @strSQL
+ ','+ case
when @Type='char' or @Type='varchar' then ''''+@Qualifier+'''+'+ @Column +'+'''+@Qualifier+''' as '+ @Column
when @Type='datetime' or @Type='smalldatetime' then ''''+@Qualifier+'''+cast('+ @Column +' as varchar)+'''+@Qualifier+''' as '+ @Column
else @Column
end
fetch cur_Column into @Column,@Type
end
close cur_Column
deallocate cur_Column
set @strSQL=stuff(@strSQL,1,1,'')
set @strSQL = 'select '+ @strSQL + ' into '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.##tmp_OUT from ' +@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.'+@TABLE_NAME + ' ' + @strWhere
print @strSQL
execute(@strSQL)
SET @strSQL = 'bcp "select * from '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.##tmp_OUT" queryout '+@FileName+' -c -t, -T -S DESKTOP15931'
--Excute xp_cmdshell
EXEC master..xp_cmdshell @strSQL
--drop temp table
set @strSQL =' drop table '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.##tmp_OUT'
execute(@strSQL) |
Partager