forked from MichelleUfford/sql-scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathbcp_script_generator.sql
More file actions
103 lines (76 loc) · 4.12 KB
/
bcp_script_generator.sql
File metadata and controls
103 lines (76 loc) · 4.12 KB
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
/**********************************************************************************************************
NAME: bcp_script_generator.sql
SYNOPSIS: Generates bcp scripts using SQL Server metadata
DEPENDENCIES: The following dependencies are required to execute this script:
- SQL Server 2005 or newer
AUTHOR: Michelle Ufford, http://sqlfool.com
CREATED: 2012-05-17
VERSION: 1.0
LICENSE: Apache License v2
----------------------------------------------------------------------------
DISCLAIMER:
This code and information are provided "AS IS" without warranty of any kind,
either expressed or implied, including but not limited to the implied
warranties or merchantability and/or fitness for a particular purpose.
----------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
-- DATE VERSION AUTHOR DESCRIPTION --
---------------------------------------------------------------------------------------------------------
20150619 1.0 Michelle Ufford Open Sourced on GitHub
**********************************************************************************************************/
-- User-defined variables --
DECLARE @tableToBCP NVARCHAR(128) = 'sandbox.dbo.example_table'
, @Top VARCHAR(10) = NULL -- Leave NULL for all rows
, @Delimiter VARCHAR(4) = '|'
, @UseNULL BIT = 1
, @OverrideChar CHAR(1) = '~'
, @MaxDop CHAR(1) = '1'
, @Directory VARCHAR(256) = 'D:\dba\mufford\scripts';
-- Script-defined variables --
DECLARE @columnList TABLE (columnID INT);
DECLARE @bcpStatement NVARCHAR(MAX) = 'BCP "SELECT '
, @currentID INT
, @firstID INT;
INSERT INTO @columnList
SELECT column_id
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableToBCP)
ORDER BY column_id;
IF @Top IS NOT NULL
SET @bcpStatement = @bcpStatement + 'TOP (' + @Top + ') ';
SELECT @firstID = MIN(columnID) FROM @columnList;
WHILE EXISTS(SELECT * FROM @columnList)
BEGIN
SELECT @currentID = MIN(columnID) FROM @columnList;
IF @currentID <> @firstID
SET @bcpStatement = @bcpStatement + ',';
SELECT @bcpStatement = @bcpStatement +
CASE
WHEN user_type_id IN (231, 167, 175, 239)
THEN 'CASE WHEN ' + name + ' = '''' THEN '
+ CASE
WHEN is_nullable = 1 THEN 'NULL'
ELSE '''' + REPLICATE(@OverrideChar, max_length) + ''''
END
+ ' WHEN ' + name + ' LIKE ''%' + @Delimiter + '%'''
+ ' OR ' + name + ' LIKE ''%'' + CHAR(9) + ''%''' -- tab
+ ' OR ' + name + ' LIKE ''%'' + CHAR(10) + ''%''' -- line feed
+ ' OR ' + name + ' LIKE ''%'' + CHAR(13) + ''%''' -- carriage return
+ ' THEN '
+ CASE
WHEN is_nullable = 1 THEN 'NULL'
ELSE '''' + REPLICATE(@OverrideChar, max_length) + ''''
END
+ ' ELSE ' + name + ' END'
ELSE name
END
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableToBCP)
AND column_id = @currentID;
DELETE FROM @columnList WHERE columnID = @currentID;
END;
SET @bcpStatement = @bcpStatement + ' FROM ' + @tableToBCP
+ ' WITH (NOLOCK) OPTION (MAXDOP 1);" queryOut '
+ @Directory + REPLACE(@tableToBCP, '.', '_') + '.dat -S' + @@SERVERNAME
+ ' -T -t"' + @Delimiter + '" -c -C;'
SELECT @bcpStatement;