Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
Добрый день, прошу помочь при написании скрипта, который должен синхронизировать структуру двух таблиц. Структура таб2 должна соответствовать таб1. Необходимо это для обновления данных в таб1, которые я получаю в форме таб2. К сожалению, структура таб2 непостоянна и от меня это не зависит, постоянно там то появиться новый столбец, то какого-то нет или тип данных не соответствует. Бардак короче. Вручную мне уже надоело менять структуру, поэтому пытаюсь эту проблемму как-то автоматизировать.
Итак, разницу в столбцах по названию между таблицами узнаю следующим образом:
with x as(
		SELECT * FROM information_schema.columns
WHERE TABLE_NAME='таб1'
	)
	,y as(
		SELECT * FROM information_schema.columns
WHERE TABLE_NAME='таб2'
	)	
SELECT x.* FROM x
	LEFT JOIN y ON x.COLUMN_NAME=y.COLUMN_NAME
WHERE y.COLUMN_NAME IS NULL
GO


Но вот дальше я как-то не могу въехать. По идее alter table add/drop column, но практически не получается. Может я синтаксически неправильно составляю запрос. Ведь столбцов которые надо добавить/удалить теоретически может быть больше чем один. Как их вставить - не пойму, весь форум перечитал!
19 мар 12, 17:25    [12275714]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
Может я вообще впринципе неправильно делаю?
19 мар 12, 17:26    [12275724]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
Неужели нет никаких идей?!
19 мар 12, 19:51    [12276776]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
andrej2005,

red-gate sql compare
или вот тут ссылка проскакивала недавно https://www.sql.ru/faq/faq_topic.aspx?fid=805
19 мар 12, 22:05    [12277426]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
может ошибка в этом
Guest
andrej2005
По идее alter table add/drop column

да
andrej2005
Может я синтаксически неправильно составляю запрос.

вы не пишете alter table add/drop column. может ошибка в этом?
19 мар 12, 22:41    [12277652]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
А может проще пересоздавать таблицу?

Только ведь что-куда-к-чему всё равно ведь ручками придётся налаживать.
19 мар 12, 22:48    [12277698]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
andrej2005
К сожалению, структура таб2 непостоянна и от меня это не зависит, постоянно там то появиться новый столбец, то какого-то нет или тип данных не соответствует.
Мне кажется вы не ту задачу себе ставите. Надо выяснить почему меняется таблица и откуда там данные.
Просто так таблица меняться не будет. Возможно эта таблица создаётся заново, данные берутся из вне. Тогда банально неправильно используются инструменты или стратегия закачки.

Особенно мне непонятно с окружением. Как там NOT NULL столбцы поживают, ограничения и индексы. Таблица, это ведь не только колонки.
19 мар 12, 22:51    [12277716]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
Владимир Затуливетер
andrej2005,

red-gate sql compare
или вот тут ссылка проскакивала недавно https://www.sql.ru/faq/faq_topic.aspx?fid=805


Не, такой вариан не катит. Нормальные инструменты мне никто не купит, нелицензированный софт устанавливать нельзя, а фри-софты кривые и недоработанные. Поэтому, в моем случае, лучше выскользнуть своими силами, решив определенную проблемму с помощью скрипта.
20 мар 12, 11:51    [12279743]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
может ошибка в этом
andrej2005
По идее alter table add/drop column

да
andrej2005
Может я синтаксически неправильно составляю запрос.

вы не пишете alter table add/drop column. может ошибка в этом?


В случае инструкции alter table add/drop column необходимо указывать наименование и тип данных для каждого столбца, а select возвращает набор даных, которые отсутствуют во второй таблице. Во второй таблице может отсутсвовать несколько столбцов. Вот этот то набор я пока-что и не представляю как проработать, в цикле чтоли?
20 мар 12, 12:03    [12279861]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
может ошибка в этом
Guest
andrej2005,

информацию о типах столбцов не взять ни откуда?
автор
в цикле чтоли?

с циклом что-то не получилось или что?

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

таб2 - таблица издателя,
таб1 - таблица на вашей стороне для получения внешних данных которая каждый раз очищается
?
20 мар 12, 12:17    [12279975]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
Mnior
andrej2005
К сожалению, структура таб2 непостоянна и от меня это не зависит, постоянно там то появиться новый столбец, то какого-то нет или тип данных не соответствует.
Мне кажется вы не ту задачу себе ставите. Надо выяснить почему меняется таблица и откуда там данные.
Просто так таблица меняться не будет. Возможно эта таблица создаётся заново, данные берутся из вне. Тогда банально неправильно используются инструменты или стратегия закачки.

Особенно мне непонятно с окружением. Как там NOT NULL столбцы поживают, ограничения и индексы. Таблица, это ведь не только колонки.


Изменение схемы таблиц от меня не зависит. Я получаю раз в неделю от генерального клиента таблицы с данными, конкретно четыре таблички. Эти данные мне и надо актуализировать. Я их просил, чтоб посылали по одной схеме - не получается! Обязательно есть разница - в результате update вылетает с ошибкой. У клиента каждую таблицу обрабатывает один человек, скажу даже больше - видел как один из них обрабатывал данные в excel а потом загонял в mdb! Поэтому сами понимаете какое тут положение. Даже primary key никто не устанавливает, приходится все вручную делать.
20 мар 12, 12:17    [12279982]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
invm
Member

Откуда: Москва
Сообщений: 9833
Примерно так:
declare @table1 sysname = 'Table1', @table2 sysname = 'Table2';

with t1 as
(
 select
  COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
 from
  INFORMATION_SCHEMA.COLUMNS
 where
  TABLE_NAME = @table1
),
t2 as
(
 select
  COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
 from
  INFORMATION_SCHEMA.COLUMNS
 where
  TABLE_NAME = @table2
)
select
 case
  when t2.COLUMN_NAME is null then 'alter ' + quotename(@table1) + ' drop column ' + quotename(t1.COLUMN_NAME) + '; '
  when t1.COLUMN_NAME is null then 'alter ' + quotename(@table1) + ' add ' + quotename(t2.COLUMN_NAME) + ' тип данных; '
  when
   not exists(
    select t1.IS_NULLABLE, t1.DATA_TYPE, t1.CHARACTER_MAXIMUM_LENGTH, t1.NUMERIC_PRECISION, t1.NUMERIC_SCALE
    intersect
    select t2.IS_NULLABLE, t2.DATA_TYPE, t2.CHARACTER_MAXIMUM_LENGTH, t2.NUMERIC_PRECISION, t2.NUMERIC_SCALE
   ) then 'alter ' + quotename(@table1) + ' alter column ' + quotename(t1.COLUMN_NAME) + ' тип данных; '
 end
from
 t1 full join
 t2 on t2.COLUMN_NAME = t1.COLUMN_NAME;
Формирование строки типа данных допилите сами. Ключи, констрейнты и т.п. тоже допилите сами.

Кстати, dbForge Schema Compare for SQL Server бесплатен.
20 мар 12, 12:18    [12279993]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
может ошибка в этом
andrej2005,

информацию о типах столбцов не взять ни откуда?
автор
в цикле чтоли?

с циклом что-то не получилось или что?
?

да, с циклом не получается

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

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

автор
таб2 - таблица издателя,
таб1 - таблица на вашей стороне для получения внешних данных которая каждый раз очищается

да, таб1 я раз в неделю актуализирую из таб2
20 мар 12, 12:27    [12280082]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
может ошибка в этом
Guest
andrej2005
Изменение схемы таблиц от меня не зависит. Я получаю раз в неделю от генерального клиента таблицы с данными, конкретно четыре таблички. Эти данные мне и надо актуализировать. Я их просил, чтоб посылали по одной схеме - не получается! Обязательно есть разница - в результате update вылетает с ошибкой. У клиента каждую таблицу обрабатывает один человек, скажу даже больше - видел как один из них обрабатывал данные в excel а потом загонял в mdb! Поэтому сами понимаете какое тут положение. Даже primary key никто не устанавливает, приходится все вручную делать.

если вам нужен именно update а не drop table-create table, то правильно что валится с ошибкой.

у вас есть таблица с данными, с этими данными вы что-то делаете.
как можно взять и добавить неизвестное количество непонятных столбцов? что вы с ними будете делать?

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

и что это за данные такие? каждая табличка что-то вроде набора коэффициентов/показателей?
тогда может правильнее ее транспонировать слегка и меняющиеся столбцы превратить в строки?

было:
цель1 показатель1 показатель2 коэф1 коэф2 коэф3
цель2 показатель1 показатель2 коэф1 коэф2 коэф3

внезапно стало:
цель1 показатель1 пок-ль2 коэф1 пок.3 коэф2 коэф33 коэф700 знач100500
цель2 показатель1 пок-ль2 коэф1 пок.3 коэф2 коэф33 коэф700 знач100500

сделать:
цель1 показатель1
цель1 пок-ль2
цель1 коэф1
цель1 пок.3
цель1 коэф2
цель2 знач100500
и т.д.
20 мар 12, 12:31    [12280129]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
может ошибка в этом
если вам нужен именно update а не drop table-create table, то правильно что валится с ошибкой.

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

Нет, названия столбцов не меняются, просто меняется их количество, обычно одного-двух нехватает в той или другой таблице, может измениться тип данных (допустим с nvarchar(50) на nvarchar(40)). Интересно, как они это делают! Наверно это все эти импорты/экспорты с excel меняют тип даных...

Ладно, пока разбираюсь с подсказкой invm.
20 мар 12, 12:58    [12280347]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
может ошибка в этом
Guest
andrej2005
может ошибка в этом
если вам нужен именно update а не drop table-create table, то правильно что валится с ошибкой.
Как работает update и почему вылетает с ошибкой я себе представляю

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

Нет, названия столбцов не меняются, просто меняется их количество, обычно одного-двух нехватает в той или другой таблице

не представляю что вы делаете в своей системе со столбцами которых вообще в принципе не было, а потом вы их раз - и заимпортили. и они теперь есть в вашей таблице. и чего? вся система внезапно начала их понимать?
20 мар 12, 13:13    [12280515]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
Aleksey V.P.
Member

Откуда: Москва
Сообщений: 575
andrej2005,

Попробуй посмотреть на задачу с другой стороны. Актуализируй данные через промежуточные таблицы:
1. Залить в промежуточные таблицы данные из Таб2
2. Почистить данные, получить отчёт о корректности данных
3. Актуализировать данные в таблице Таб1 из промежуточных таблиц.
20 мар 12, 13:26    [12280639]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
может ошибка в этом
не представляю что вы делаете в своей системе со столбцами которых вообще в принципе не было, а потом вы их раз - и заимпортили. и они теперь есть в вашей таблице. и чего? вся система внезапно начала их понимать?

Итак!
- случай когда нет какого-либо столбца во втрой таблице, но он есть и нужен в первой - создаю во второй такой же столбец и импортирую туда данные из первой на основании реляции 1:1, чтобы при апдейте таб2->таб1 не потерять данные.
- случай когда нет какого-либо столбца в первой таблице, но он есть и не нужен во второй- просто его дропаю.
20 мар 12, 13:28    [12280658]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
Aleksey V.P.
Member

Откуда: Москва
Сообщений: 575
andrej2005
может ошибка в этом
не представляю что вы делаете в своей системе со столбцами которых вообще в принципе не было, а потом вы их раз - и заимпортили. и они теперь есть в вашей таблице. и чего? вся система внезапно начала их понимать?

Итак!
- случай когда нет какого-либо столбца во втрой таблице, но он есть и нужен в первой - создаю во второй такой же столбец и импортирую туда данные из первой на основании реляции 1:1, чтобы при апдейте таб2->таб1 не потерять данные.
- случай когда нет какого-либо столбца в первой таблице, но он есть и не нужен во второй- просто его дропаю.


Не надо дропать, просто проигнорируй.
20 мар 12, 13:30    [12280682]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
может ошибка в этом
Guest
andrej2005
может ошибка в этом
не представляю что вы делаете в своей системе со столбцами которых вообще в принципе не было, а потом вы их раз - и заимпортили. и они теперь есть в вашей таблице. и чего? вся система внезапно начала их понимать?

Итак!
- случай когда нет какого-либо столбца во втрой таблице, но он есть и нужен в первой - создаю во второй такой же столбец и импортирую туда данные из первой на основании реляции 1:1, чтобы при апдейте таб2->таб1 не потерять данные.
- случай когда нет какого-либо столбца в первой таблице, но он есть и не нужен во второй- просто его дропаю.

т.е. вы ничего кроме импорта и хранения с этими данными не делаете?
20 мар 12, 13:31    [12280689]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
может ошибка в этом
andrej2005
пропущено...

Итак!
- случай когда нет какого-либо столбца во втрой таблице, но он есть и нужен в первой - создаю во второй такой же столбец и импортирую туда данные из первой на основании реляции 1:1, чтобы при апдейте таб2->таб1 не потерять данные.
- случай когда нет какого-либо столбца в первой таблице, но он есть и не нужен во второй- просто его дропаю.

т.е. вы ничего кроме импорта и хранения с этими данными не делаете?

Почему, делаю. Эти таблицы содержат информацию о электрических сигналах и соединены реляциями с другими таблицами.
Aleksey V.P.
Не надо дропать, просто проигнорируй.

Тоже правильно, но я потом еще использую select* в других запросах и если структура не одинаковая, то вылетает.
20 мар 12, 14:00    [12280985]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
может ошибка в этом
Guest
andrej2005
еще использую select* в других запросах и если структура не одинаковая, то вылетает.

вроде ошибка только в апдейте вылетала, не?
20 мар 12, 14:06    [12281044]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
может ошибка в этом
andrej2005
еще использую select* в других запросах и если структура не одинаковая, то вылетает.

вроде ошибка только в апдейте вылетала, не?

Вообщето ошибка не вылетает нигде, у меня все подогнано, просто я это делаю вручную. А хотелось бы с помощью скрипта. Наши друзья из клиентской фирмы чудят без баяна - нехочу этим заморачивать вам голову. Там надо делать кучу проверок, кто чего удалил, кто чего добавил, если мы не используем этот или иной сигнал. Короче, это объяснять долго. Select* использую в инструкции Insert для новых сигналов, которых нет в таб1, поэтому вставка новых сигналов не пройдет, если таблицы неодинаковые. Хотя, если select определить конкретно, то сработает, но тоже не во всех случаях, т.к. если тип данных не одинаковый - то вылетит. Короче, проще таб2 привести к схеме таб1.
20 мар 12, 14:27    [12281285]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
Glory
Member

Откуда:
Сообщений: 104751
Не проще создать view на основе таб2 ?
20 мар 12, 14:30    [12281331]     Ответить | Цитировать Сообщить модератору
 Re: синхронизация структуры двух таблиц  [new]
andrej2005
Member

Откуда:
Сообщений: 142
Если тип данных не одинаковый - то вылетит INSERT. Не получается вставить nvarchar(50) в nvarchar(40).
20 мар 12, 15:32    [12281974]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить