Как удалить все символы новой строки из всех таблиц / столбцов

Я работаю над экспортом базы данных из SQL Server в Snowflake, и я столкнулся с проблемой, когда у нас есть неизвестное количество столбцов с комментариями пользователей и новыми строчными символами. Проблема в том, что в базе данных более 280 таблиц, и я не хочу просматривать каждую таблицу вручную. Мне было интересно, есть ли способ автоматизировать это.

В настоящее время я использую SSIS для экспорта данных и просто выполняю выборочную замену в столбцах, которые, как мне кажется, имеют символ новой строки.

Я тоже использовал этот скрипт:

declare @NewLine char(2) set @NewLine=char(13)+char(10) update Projects set [PR_ITComment] =Replace([PR_ITComment] , @NewLine,'') WHERE [PR_ITComment] like '%' +@NewLine +'%'

sql-server snowflake-cloud-data-platform ssis
person unreal    schedule 20.01.2017    source источник
comment
Это будет ужасно медленно сделать это с динамическим скриптом на 280 столах. По сути, вы говорите, что хотите сделать эту замену для каждого столбца каждой строки в каждой таблице. Или это всегда просто столбец с именем PR_ITComment? И существует ли он для каждой отдельной таблицы или вам нужно обновлять только таблицы с таким именем столбца?   -  person Sean Lange    schedule 21.01.2017
comment
Производительность и время, необходимое для запуска, не являются проблемой. Итак, да, я ищу динамический сценарий, поскольку мы не знаем точно, в каких столбцах возникает эта проблема, поэтому сценарий, который просматривает каждый столбец в каждой таблице, - это то, что я ищу.   -  person unreal    schedule 21.01.2017
comment
Кстати, как вы экспортируете данные? Если вы можете экспортировать в CSV с кавычками, я считаю, что Snowflake должна загрузить его, включая новые строки.   -  person Marcin Zukowski    schedule 23.01.2017
comment
@MarcinZukowski Изначально у меня было две проблемы: одна - новые строки, а вторая - запятая, поскольку разделитель не был достаточно уникальным. Также Snowflake настроен на отображение любой новой строки как новой строки.   -  person unreal    schedule 23.01.2017
comment
Есть способ сделать это - я опубликовал более подробный ответ ниже.   -  person Marcin Zukowski    schedule 24.01.2017

Ответы (4)


arrow_upward
0
arrow_downward

Это похоже на ответ Шона Ланге, но разрешает одно обновление для каждой таблицы, а не одно обновление для каждого столбца.

--declare @schema nvarchar(256) = 'dbo';
--declare @table  nvarchar(256) = 'table';
declare @sql    nvarchar(max) = '';

  set @sql += (select 'update '+t.table_schema+'.'+t.table_name+' set ' +stuff(
    ( select ', ['+i.column_name +']=replace(replace(['+i.column_name+'],char(10),''''),char(13),'''')'+char(10) 
        from information_schema.columns i 
        where i.table_schema=t.table_schema 
          and i.table_name=t.table_name 
          and i.data_type in ('char','nchar','varchar','nvarchar','text','ntext') 
        order by i.ordinal_position 
        for xml path('')),1,1,'')+';'+char(10)
    from information_schema.tables t
    where t.table_type='base table' 
      --and t.table_schema = @schema
      --and t.table_name   = @table
    for xml path (''), type).value('.','varchar(max)')

  --print @sql
  select @sql
  --exec sp_executesql @sql
person SqlZim    schedule 20.01.2017
comment
Спасибо @SqlZim и Шону Ланге, это привело меня на правильный путь - person unreal; 22.01.2017
comment
Еще один вопрос, когда я запускаю этот скрипт, похоже, что он не распечатывает все операторы обновления, поскольку похоже, что последний оператор отключен, что ограничивает это? - person unreal; 23.01.2017
comment
@unreal print имеет ограничение в 4000/8000 символов. отредактировал ответ, чтобы использовать select. sqlservercentral.com/scripts/Print/63240 - person SqlZim; 23.01.2017

arrow_upward
1
arrow_downward

Вот один из способов решить эту проблему. Это использует динамический sql, поэтому вам не нужно прибегать к циклам. Возможно, вы захотите немного изменить это в соответствии со своими потребностями. Вы можете добавить еще один предикат, чтобы исключить определенные таблицы или подобные вещи из списка. Это работает так, что создается довольно большое количество операторов обновления. Затем вы просто выполняете массивную струну.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'Update ' + quotename(t.name) + ' set ' + quotename(c.name) + ' = replace(Replace(' + quotename(c.name) + ', char(10), ''''), char(13), '''');' 
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join sys.systypes st on st.xtype = c.system_type_id
where st.name in ('text', 'ntext', 'varchar', 'nvarchar', 'char', 'nchar')

select @SQL

--Once you are comfortable with the output you can uncomment the line below to actually run this.
--exec sp_executesql @SQL
person Sean Lange    schedule 20.01.2017

arrow_upward
0
arrow_downward

Если вы можете экспортировать данные с использованием кавычек (что является стандартным способом CSV), Snowflake может просто загружать данные с новыми строками. Вы также можете использовать экранирование, но лучше использовать кавычки.

Пример файла с 3 строками

$ cat data.csv
1,"a",b
2,c,"d1
d2"
3,"e1,e2,
e3",f

Пример SQL и вывод:

create or replace table x(nr int, a string, b string);
put file://data.csv @%x;
copy into x file_format = (field_optionally_enclosed_by = '"');
select * from x;
----+--------+----+
 NR |   A    | B  |
----+--------+----+
 1  | a      | b  |
 2  | c      | d1 |
    |        | d2 |
 3  | e1,e2, | f  |
    | e3     |    |
----+--------+----+
person Marcin Zukowski    schedule 24.01.2017

arrow_upward
0
arrow_downward

У меня была такая же проблема при экспорте данных в Excel. Вы можете заменить char (13) и char (10) с помощью ''. Это будет работать.

Это просто заменить в запросе задачи «Выполнение SQL» или SP SSIS. Или вы можете использовать его в операторе обновления, чтобы обновлять записи навсегда.

person Parag    schedule 27.02.2017