Как сравнить 2 таблицы в sql

tablediff, программа

Служебная программа tablediff используется для сравнения данных в двух таблицах на расхождение и особенно полезна для устранения несоответствия данных в топологии репликации. Эта программа может запускаться из командной строки или из пакетного файла и служит для выполнения следующих задач:

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

Быстрое сравнение, сравнивающее только схемы и количество строк.

Сравнение на уровне столбцов.

Формирование скрипта Transact-SQL для исправления несоответствий на целевом сервере и обеспечения конвергенции исходной и целевой таблиц.

Запись результатов операции в файл вывода или в таблицу целевой базы данных.

Синтаксис

Аргументы

[ -? ]
Возвращает список поддерживаемых параметров.

-sourceserver source_server_name[ \ instance_name]
Имя исходного сервера. Укажите имя_исходного_сервера для экземпляра SQL Serverпо умолчанию. Укажите имя_исходного_сервера \ имя_экземпляра для именованного экземпляра SQL Server.

-sourcedatabase source_database
Имя базы данных-источника.

-sourcetable source_table_name
Имя проверяемой исходной таблицы.

-sourceschema source_schema_name
Владелец схемы исходной таблицы. Владельцем таблицы по умолчанию считается dbo.

-sourcepassword source_password
Пароль для имени входа, используемого для подключения к исходному серверу с помощью проверки подлинности SQL Server .

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

-sourceuser source_login
Имя входа, используемое для подключения к исходному серверу с помощью проверки подлинности SQL Server . Если исходное_имя_входа не указано, для соединения с исходным сервером используется проверка подлинности Windows. По возможности используйте аутентификацию Windows.

-sourcelocked
Исходная таблица блокируется в ходе сравнения при помощи табличных подсказок TABLOCK и HOLDLOCK.

-destinationserver destination_server_name[ \ instance_name]
Имя целевого сервера. Укажите имя_целевого_сервера для экземпляра SQL Serverпо умолчанию. Укажите имя_целевого_сервера \ имя_экземпляра для именованного экземпляра SQL Server.

-destinationdatabase subscription_database
Имя целевой базы данных.

-destinationtable destination_table
Имя целевой таблицы.

-destinationschema destination_schema_name
Владелец схемы целевой таблицы. Владельцем таблицы по умолчанию считается dbo.

-destinationpassword destination_password
Пароль для имени входа, используемого для подключения к целевому серверу с помощью проверки подлинности SQL Server .

-destinationuser destination_login
Имя входа, используемое для подключения к целевому серверу с помощью проверки подлинности SQL Server . Если целевое_имя_входа не указано, для соединения с исходным сервером используется проверка подлинности Windows. По возможности используйте аутентификацию Windows.

-destinationlocked
Целевая таблица блокируется в ходе сравнения при помощи табличных указаний TABLOCK и HOLDLOCK.

-b large_object_bytes
Число байтов для сравнения столбцов, содержащих данные типа больших объектов, к которым относятся: text, ntext, image, varchar(max) , nvarchar(max) и varbinary(max) . число_байтов_больших_объектов по умолчанию имеет размер столбца. Любые данные, размер которых превышает значение число_байтов_больших_объектов , не учитываются при сравнении.

-bf number_of_statements
Число инструкций Transact-SQL для записи в текущий файл скрипта Transact-SQL при использовании параметра -f . Когда число инструкций Transact-SQL превышает значение число_инструкций, создается новый файл скрипта Transact-SQL .

-c
Сравнение на уровне столбцов.

-dt
Удаление таблицы результатов, указанной в аргументе имя_таблицы, если она уже существует.

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

-f [ имя_файла ]
Формирует скрипт Transact-SQL , по которому обеспечивается конвергенция таблицы на целевом сервере и таблицы на исходном сервере. Можно дополнительно указать имя и путь для создаваемого файла скрипта Transact-SQL . Если параметр имя_файла не указан, файл скрипта Transact-SQL создается в каталоге, в котором запущена данная программа.

-o output_file_name
Полное имя и путь файла вывода.

-q
Быстрое сравнение, сравнивающее только схемы и количество строк.

-rc number_of_retries
Количество попыток повтора программой неудачно завершившейся операции.

-ri retry_interval
Интервал в секундах между повторными попытками.

-strict
Строгая проверка исходной и целевой схем.

-t connection_timeouts
Устанавливает время ожидания в секундах для соединений с исходным сервером и целевым сервером.

Возвращаемое значение

Значение	Описание
0	Успешно
1	Критическая ошибка
2	Различия таблиц

Remarks

Служебную программу tablediff нельзя использовать для обращения к серверам, отличным от SQL Server.

Таблицы со столбцами с данными типа sql_variant не поддерживаются.

По умолчанию служебная программа tablediff поддерживает перечисленные ниже сопоставления типов данных между исходными и целевыми столбцами.

Тип данных источника	Тип данных назначения
tinyint	smallint, int или bigint
smallint	int или bigint
int	bigint
timestamp	varbinary
varchar(max)	text
nvarchar(max)	ntext
varbinary(max)	image
text	varchar(max)
ntext	nvarchar(max)
image	varbinary(max)

Параметр -strict запрещает такое сопоставление и выполняет строгую проверку.

Исходная таблица при данном сравнении должна содержать хотя бы один столбец первичного ключа, столбец идентификаторов или столбец ROWGUID. При использовании параметра -strict целевая таблица также должна содержать столбец первичного ключа, столбец идентификаторов или столбец ROWGUID.

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

SQL, как сравнить две таблицы для одного и того же содержимого данных?

Как мне написать SQL-запрос, чтобы проверить, содержат ли TableA и TableB (которые имеют идентичные первичные ключи) одинаковые значения в каждом столбце?

Это означает, что эти две таблицы имеют абсолютно одинаковые данные.

Вы должны иметь возможность «МИНУС» или «ИСКЛЮЧИТЬ» в зависимости от разновидности SQL, используемой вашей СУБД.

Если запрос не возвращает ни одной строки, данные точно такие же.

Использование операторов отношения:

Измените EXCEPT на MINUS для Oracle.

Немного придирчивый момент: вышеизложенное зависит от приоритета оператора, который в соответствии со стандартом SQL зависит от реализации, поэтому YMMV. Это работает для SQL Server, для которого приоритет:

Выражения в скобках
INTERSECT
EXCEPT и UNION оценивается слева направо.

У диететбудды есть хороший ответ. В случаях, когда у вас нет MINUS или EXCEPT, один из вариантов — выполнить объединение всех таблиц, сгруппировать по всем столбцам и убедиться, что всего два:

Возвращает все идентификаторы, которые ОДИНАКОВЫ в обеих таблицах. Чтобы получить различия, измените EXISTS на NOT EXISTS.

Взяв сценарий из onedaywhen, я изменил его, чтобы он также показывал, из какой таблицы берется каждая запись.

Улучшение ответа диетитабудды.

просто для завершения процесс, хранящийся с использованием метода, кроме сравнения 2 таблиц и получения результата в одной таблице с 3 статусами ошибок, таблица ADD, DEL, GAP должна иметь одинаковый PK, вы объявляете 2 таблицы и поля для сравнения 1 или обеих таблиц

Просто используйте ps_TableGap ‘tbl1′,’Tbl2′,’fld1,fld2,fld3′,’fld4’fld5’fld6’ (необязательно)

Вы можете найти различия двух таблиц, используя комбинацию вставки всех и полного внешнего соединения в Oracle. В sql вы можете извлечь различия через полное внешнее соединение, но кажется, что вставка all/first не существует в sql! Следовательно, вместо этого вы должны использовать следующий запрос:

Хотя использование «ИЛИ» в предложении where не рекомендуется и обычно приводит к снижению производительности, вы все равно можете использовать приведенный выше запрос, если ваши таблицы не являются массивными. Если для приведенного выше запроса есть какой-либо результат, это именно различия 2 таблиц на основе сравнения полей 1,2,3,4. Для повышения производительности запроса вы также можете отфильтровать его по дате (проверьте закомментированную часть)

Умный подход к использованию NATURAL FULL JOIN для обнаружения одинаковых/разных строк между двумя таблицами.

Пример 1 — флаг состояния:

Пример 2 — фильтрация строк

Результат нулевой, но источники разные!

У меня была такая же проблема в SQL Server, и я написал этот скрипт T-SQL для автоматизации процесса (на самом деле это упрощенная версия, моя записала все различия в одну таблицу для удобства отчетности).

Обновите «MyTable» и «MyOtherTable» на имена таблиц, которые вы хотите сравнить.

Я написал это, чтобы сравнить результаты довольно неприятного представления, которое я перенес с Oracle на SQL Server. Он создает пару временных таблиц, #DataVariances и #SchemaVariances, с различиями (как вы уже догадались) в данных в таблицах и в схеме самих таблиц.

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

«Х» — любое значение.

Переключайте таблицы, чтобы увидеть различные несоответствия.

Не забудьте присоединиться к ключевым полям в ваших таблицах.

Или просто используйте оператор МИНУС с двумя операторами выбора, однако МИНУС может работать только в Oracle.

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

Сравнить T1(PK, A, B) и T2(PK, A, B).

Сначала сравните наборы первичных ключей, чтобы найти отсутствующие значения ключей с обеих сторон:

Затем перечислите все несоответствия значений:

A и B должны быть одного типа. Вы можете использовать ИНФОРМАЦИОННУЮ СХЕМУ для генерации SELECT. Не забывайте, что COALESCE также включает результаты IS NULL. Вы также можете использовать FULL OUTER JOIN и COALESCE(T1.PK,0)=COALESCE(T2.PK,0).

SQL сравнить данные из двух таблиц

Как написать SQL, чтобы проверить, что if TableA и TableB that имеют одинаковые первичные ключи, содержат одно и то же значение во всех столбцах.

Это означает, что в этих двух таблицах точно такие же данные.

Вы должны иметь возможность «МИНУС» или «ИСКЛЮЧИТЬ» в зависимости от разновидности SQL, используемого вашей СУБД.

Если запрос не возвращает строк, данные точно такие же.

Использование операторов отношения:

Изменение EXCEPT к MINUS для Oracle.

Немного придирчивый момент: вышесказанное зависит от приоритета операторов, который, согласно стандарту SQL, зависит от реализации, поэтому YMMV. Он работает для SQL Server, для которого приоритет:

Выражения в скобках
INTERSECT
EXCEPT и UNION оценивается слева направо.

У diebuddha есть хороший ответ. В случаях, когда у вас нет МИНУС или ИСКЛЮЧЕНИЕ, один из вариантов — объединить все таблицы между собой, сгруппировать по всем столбцам и убедиться, что всего два:

Вернет все ОДИНАКОВЫЕ ИДЕНТИФИКАЦИИ в обеих таблицах. Чтобы получить различия, измените EXISTS на NOT EXISTS.

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

просто для завершения процесс, сохраненный с использованием метода except для сравнения 2 таблиц и получения результата в той же таблице с 3 состояниями ошибок, таблица ADD, DEL, GAP должна иметь одинаковый PK, вы объявляете 2 таблицы и поля для сравнения 1 или обеих таблиц

Просто используйте как этот ps_TableGap ‘tbl1’, ‘Tbl2’, ‘fld1, fld2, fld3’, ‘fld4’fld5’fld6’ (необязательно)

Умный подход к использованию NATURAL FULL JOIN для обнаружения одинаковых / разных строк между двумя таблицами.

Пример 1 — флаг статуса:

Пример 2 — фильтрация строк

Дополнение к ответу диетбудды .

Вы можете найти различия двух таблиц, используя комбинацию вставки всего и полного внешнего соединения в Oracle. В sql вы можете извлечь различия с помощью полного внешнего соединения, но кажется, что insert all / first не существует в sql! Следовательно, вместо этого вы должны использовать следующий запрос:

Хотя использование OR в предложении where не рекомендуется и обычно приводит к снижению производительности, вы все равно можете использовать вышеуказанный запрос, если ваши таблицы не являются массивными. Если и есть результат по вышеуказанному запросу, то это как раз различия двух таблиц на основе сравнения полей 1,2,3,4. Для повышения производительности запроса вы также можете фильтровать его по дате (проверьте прокомментированную часть)

Результат нулевой, но источники разные!

У меня была такая же проблема в SQL Server, и я написал этот сценарий T-SQL для автоматизации процесса (на самом деле это упрощенная версия, я записал все различия в единую таблицу для облегчения отчетности).

Обновите MyTable и MyOtherTable именами таблиц, которые вы хотите сравнить.

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

Большинство ответов, кажется, игнорируют проблему, поднятую Камилем. (Именно здесь таблицы содержат идентичные строки, но разные повторяются в каждой таблице.) К сожалению, я не могу использовать его решение, потому что я работаю в Oracle. Лучшее, что мне удалось придумать, это:

Чтобы сравнить T1 (PK, A, B) и T2 (PK, A, B).

Сначала сравните наборы первичных ключей, чтобы найти недостающие значения ключей с обеих сторон:

Затем перечислите все несоответствия значений:

A и B должны быть одного типа. Вы можете использовать ИНФОРМАЦИОННУЮ СХЕМУ для генерации SELECT. Не забудьте, что COALESCE также включает результаты IS NULL. Вы также можете использовать FULL OUTER JOIN и COALESCE (T1.PK, 0) = COALESCE (T2.PK, 0).

Например, для столбцов типа varchar:

Мы можем сравнить данные из двух таблиц таблиц DB2, используя следующий простой запрос:

Шаг 1: — Выберите все столбцы, которые нам нужно сравнить, из таблицы (T1) схемы (S)

Шаг 2: — Используйте ключевое слово «Минус» для сравнения двух таблиц.

Шаг 3: — Выберите все столбцы, которые нам нужно сравнить, из таблицы (T2) схемы (S)