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
| CREATE PROCEDURE prc_export_tables
AS
BEGIN
SET NOCOUNT ON
DECLARE @bcp varchar(1024)
, @bcp_exec varchar(1024)
, @sql varchar(1024)
, @export_table sysname
, @export_columns_list varchar(max)
SELECT @bcp = 'BCP "SELECT @listeColonnes@ FROM ELSUKET.dbo.@maTable@ WHERE flag = 0" queryout @file@ -S "' + @@SERVERNAME + '" -T -c'
, @export_table = ''
UPDATE dbo.T_EXPORT_SETTINGS
SET exported = 0
WHILE @export_table IS NOT NULL
BEGIN
SET @export_table = ''
SELECT TOP (1) @export_table = export_table
, @export_columns_list = export_columns_list
FROM dbo.T_EXPORT_SETTINGS
WHERE exported = 0
SET @export_table = NULLIF(@export_table, '')
SET @bcp_exec = REPLACE(@bcp, '@maTable@', @export_table)
SET @bcp_exec = REPLACE(@bcp_exec, '@listeColonnes@', @export_columns_list)
SET @bcp_exec = REPLACE(@bcp_exec, '@file@', 'C:\' + @export_table + '.txt')
SET @sql = 'UPDATE dbo.' + @export_table + ' SET flag = 1 WHERE flag = 0'
EXEC xp_cmdshell @bcp_exec
EXEC (@sql)
UPDATE TOP (1) dbo.T_EXPORT_SETTINGS
SET exported = 1
WHERE exported = 0
END
END
GO |
Partager