· 8 years ago · May 15, 2018, 07:34 PM
1if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')
2
3 begin
4 drop table HeaderTable
5 end
6
7 SELECT
8 st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
9 sc.precision, sc.scale, sc.is_nullable
10 into HeaderTable
11 FROM
12 sys.tables st
13 inner join sys.columns sc on sc.object_id = st.object_id
14 INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id
15
16declare @table varchar(255),
17 @Database varchar(255),
18 @filepath varchar(255),
19 @filename varchar(255),
20 @filetype varchar(255),
21 @sql nvarchar(max),
22 @delimiter varchar(255),
23 @rowterminator varchar(255)
24
25 set @Database = 'StackOverflow'
26 set @filepath = 'C:Data'
27 set @filetype = '.txt'
28 set @delimiter = 'WhimmyWhammyWozzle'
29 set @rowterminator = 'WubaLubaDubDub'
30
31 declare c cursor local for
32
33 select name from sys.tables with (Nolock)
34
35 open c
36
37 fetch from c into @table
38
39 while @@FETCH_STATUS = 0
40 begin
41
42 SET @filename = @table
43
44 --output to txt
45 set @sql = N'declare @bcp varchar(4000)
46 set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
47 + @filepath + @filename + @filetype + ' -t "' + @delimiter + '" -r "'
48 + @rowterminator + '" -c -T -d ' + @Database + '''
49 print @bcp
50
51 EXECUTE master.dbo.xp_cmdshell @BCP'
52
53 print @sql
54 --exec sp_executesql @sql
55
56 fetch next from c into @table
57 end
58
59 close c
60
61 deallocate c
62
63create table HeaderTable
64 (Table_Name varchar(255),
65 Column_Name varchar(255),
66 Data_type varchar(255),
67 Max_Length varchar(255),
68 precision varchar(255),
69 scale varchar(255),
70 is_nullable varchar(255))
71
72 And now, we are going to bulk insert our Schema into HeaderTable:
73
74 set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
75 + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
76 print @sql
77 --exec sp_executesql @sql
78
79 We will have to tidy it up a bit, to make the next steps easier:
80
81 update HeaderTable
82 set Max_Length = 'max'
83 where Max_length = -1
84
85 update HeaderTable
86 set Max_Length = '(' + Max_Length + ')'
87
88 update HeaderTable
89 set Max_Length = ''
90 where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
91 'date','datetime', 'uniqueidentifier', 'sysname', 'bit')
92
93 4. Re-create & Bulk Insert each table from its text file
94
95 And here’s where things get cool again. We are going to loop through HeaderTable and re-create each table, concatenating the Create statement with STUFF(). Don’t ask me how stuff() works - an old coworker (Mike Ignatoski) gave this to me years ago. Reliable sources say he originally got it from some guy named Solomon.
96
97 declare @table varchar(255),
98 @column_string nvarchar(max),
99 @sql nvarchar(max),
100 @string nvarchar(max),
101 @filepath varchar(255),
102 @filename varchar(255),
103 @filetype varchar(255),
104 @sql nvarchar(max),
105 @delimiter varchar(255),
106 @rowterminator varchar(255)
107
108
109 set @filepath = 'C:Data'
110 set @filetype = '.txt'
111 set @delimiter = 'WhimmyWhammyWozzle'
112 set @rowterminator = 'WubaLubaDubDub'
113
114 declare c cursor local for
115
116 select distinct Table_Name from HeaderTable
117 where Table_Name != 'HeaderTable'
118
119 open c
120 fetch from c into @table
121
122 while @@FETCH_STATUS = 0
123 begin
124
125 set @string = null
126
127 set @string = (select stuff( (
128 select ', ' + Column_Name + ' ' + Data_type + Max_Length from HeaderTable
129 where Table_Name = @table
130 for xml path ('')),1,2,''))
131
132 print @string
133
134 set @sql = ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
135 create table ' + @table + ' (' + @string + ') end'
136
137 print @sql
138 exec sp_executesql @sql
139
140 --populate the table
141 set @sql = 'BULK INSERT ' + @table + ' FROM ''' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
142 + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'
143
144 print @sql
145 exec sp_executesql @sql
146
147 fetch next from c into @table
148
149 end
150
151 close c
152
153 deallocate c
154
155declare @table varchar(255),
156 @Database varchar(255),
157 @filepath varchar(255),
158 @filename varchar(255),
159 @filetype varchar(255),
160 @sql nvarchar(max),
161 @delimiter varchar(255),
162 @rowterminator varchar(255)
163
164 set @Database = 'StackOverflow'
165 set @filepath = 'C:Data'
166 set @filetype = '.txt'
167 set @delimiter = 'WhimmyWhammyWozzle'
168 set @rowterminator = 'WubaLubaDubDub'
169
170 --create database header
171
172 if exists (select name from sys.tables st with (Nolock) where name = 'HeaderTable')
173
174 begin
175 drop table HeaderTable
176 end
177
178 SELECT
179 st.name, sc.name 'Column_Name', t.Name 'Data_type',sc.max_length 'Max_Length',
180 sc.precision, sc.scale, sc.is_nullable
181 into HeaderTable
182 FROM
183 sys.tables st
184 inner join sys.columns sc on sc.object_id = st.object_id
185 INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id
186
187 select * from HeaderTable
188
189 declare c cursor local for
190
191 select name from sys.tables so with (Nolock)
192
193 open c
194
195 fetch from c into @table
196
197 while @@FETCH_STATUS = 0
198 begin
199
200 SET @filename = @table
201
202
203 --output to txt
204 set @sql = N'declare @bcp varchar(4000)
205 set @bcp = ''bcp " select top 10000 * from ' + @table + ' " queryout '
206 + @filepath + @filename + @filetype + ' -t "' + @delimiter + '" -r "'
207 + @rowterminator + '" -c -T -d ' + @Database + '''
208 print @bcp
209
210 EXECUTE master.dbo.xp_cmdshell @BCP'
211
212 print @sql
213 exec sp_executesql @sql
214
215 fetch next from c into @table
216 end
217
218 close c
219
220 deallocate c
221
222declare @table varchar(255),
223 @column_string nvarchar(max),
224 @sql nvarchar(max),
225 @string nvarchar(max),
226 @filepath varchar(255),
227 @filename varchar(255),
228 @filetype varchar(255),
229 @delimiter varchar(255),
230 @rowterminator varchar(255)
231
232
233 set @filepath = 'C:Data'
234 set @filetype = '.txt'
235 set @delimiter = 'WhimmyWhammyWozzle'
236 set @rowterminator = 'WubaLubaDubDub'
237
238 --restore header
239 if exists (select name from sys.tables where name = 'HeaderTable')
240 begin
241 drop table HeaderTable
242 end
243
244 create table HeaderTable
245 (Table_Name varchar(255),
246 Column_Name varchar(255),
247 Data_type varchar(255),
248 Max_Length varchar(255),
249 precision varchar(255),
250 scale varchar(255),
251 is_nullable varchar(255))
252
253 set @sql = 'BULK INSERT HeaderTable FROM ''' + @filepath + 'HeaderTable'+ @filetype + ''' WITH (FIELDTERMINATOR = '''
254 + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''')'
255 print @sql
256 exec sp_executesql @sql
257
258 --make some changes so that we can concatenate our create tables more easily
259 update HeaderTable
260 set Max_Length = 'max'
261 where Max_length = -1
262
263 update HeaderTable
264 set Max_Length = '(' + Max_Length + ')'
265
266 update HeaderTable
267 set Max_Length = ''
268 where Data_type in ( 'int', 'bigint', 'smallint', 'tinyint',
269 'date','datetime', 'uniqueidentifier', 'sysname', 'bit')
270
271 select * from HeaderTable
272
273 --restore DB
274
275 declare c cursor local for
276
277 select distinct name from sys.columns
278 where name != 'HeaderTable'
279
280 open c
281 fetch from c into @table
282
283 while @@FETCH_STATUS = 0
284 begin
285
286 set @string = null
287
288 set @string = (select stuff( (
289 select ', ' + Column_Name + ' ' + Data_type + Max_Length from HeaderTable
290 where name = @table
291 for xml path ('')),1,2,''))
292
293 print @string
294
295 set @sql = ' if not exists (select top 1 name from sys.tables where name = ''' + @table + ''') begin
296 create table ' + @table + ' (' + @string + ') end'
297
298 print @sql
299 --exec sp_executesql @sql
300
301 set @sql = 'BULK INSERT ' + @table + ' FROM ' + '' + @filepath + @table + '.txt'' WITH (FIELDTERMINATOR = '''
302 + @delimiter + ''', ROWTERMINATOR = ''' + @rowterminator + ''' )'
303
304 print @sql
305 --exec sp_executesql @sql
306
307 fetch next from c into @table
308
309 end
310
311 close c
312
313 deallocate c