Копировать из всех таблиц, вставить во все таблицы (альтернатива sp_MSforeachtable)

Question

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

Цель: скопировать данные из всех таблиц в моей базе данных в таблицы с одинаковыми именами с другой схемой

Одна быстрая заметка о sp_MSforeachtable, Большую часть времени, когда люди задают вопросы здесь, в SO, об этой хранимой процедуре, есть какой-то ответ или другое высказывание, что мы не должны использовать неподдерживаемые функции. Это просто неправда, но мы не должны иметь практики и разрабатывать решения, основанные на неподдерживаемых функциях, в основном потому, что они могут исчезнуть. Но в какой-то конкретный момент времени, если существует неподдерживаемая функция и она делает именно то, что нам нужно сделать правильно, одноразовый стиль, тогда просто считайте, что вам повезло, и непременно используйте ее, просто будьте осторожны с неожиданным поведением. При использовании таких функций лучше придерживаться простых операций, вывод которых можно легко и быстро проверить.

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

Answer 1

Я часто подхожу к этой проблеме, генерируя запросы, которые возвращают операторы, а затем копирую и вставляю эти операторы в новое окно запросов и выполняю их. Мне нравится такой подход, потому что я вижу свои операторы до их выполнения и также могу использовать анализатор для быстрого выявления проблем с ними. Имея это в виду, вот мой запрос:

SELECT DISTINCT ''
    + ' INSERT INTO ' + 'dbo.' + QUOTENAME(name)
    + ' (' + dbo.COLUMN_NAMES('dbo', name) + ')'
    + ' SELECT ' + dbo.COLUMN_NAMES('dbo', name)
    + ' FROM ' + 'db_owner.' + QUOTENAME(name)
FROM sys.tables
-- add your own WHERE clauses to only execute against specific tables

Это так просто. Это вернет список операторов INSERT INTO ... SELECT FROM..., которые я могу просто скопировать и вставить в новое окно запроса.

Если вам интересно, почему не просто использовать SELECT *, это просто потому, что если в вашей таблице есть столбцы идентификаторов, вам нужно будет явно указать имена столбцов, а также добавить операторы SET_IDENTITY до и после.

Функция COLUMN_NAMES - это довольно понятная функция, которая возвращает имена столбцов через запятую для указанной таблицы:

CREATE FUNCTION COLUMN_NAMES
(
    @tableschema VARCHAR(MAX),
    @tablename VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

RETURN (
    REPLACE(
        (SELECT QUOTENAME(COLUMN_NAME) AS 'data()'
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA=@tableschema AND TABLE_NAME=@tablename ORDER BY ORDINAL_POSITION FOR XML PATH('')),
         ' ',', '))

END
GO