Как объединить много похожих таблиц?

Question

У меня есть много таблиц, которые имеют одинаковую структуру и сходные имена таблиц, и я ищу способ объединить несколько столбцов из всех них в новую таблицу с двумя дополнительными столбцами: автоматически сгенерированным целым числом PK и именем исходной таблицы., например,

UniqueID SourceID, Xcoord, Ycoord, Zcoord, SourceTable

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

SELECT [name]
INTO PointTables
FROM [Surveys].[sys].[tables]
where [name] like '%CoordDB'

Answer 1

Ответ от Чарли Лукмана был хорошим началом, но почему-то работал только на первом столе. Я просмотрел несколько других постов и обнаружил курсоры, которые позволяют вам работать по одной строке за раз, используя цикл WHILE для построения / объединения нескольких команд INSERT INTO. Хотя это работает в моем тесте из 5 таблиц, я беспокоюсь о производительности, когда добираюсь до 100 или 1000 таблиц.

declare @command nvarchar(max) 
declare @tblname varchar(50)
declare TableCursor Cursor
    FOR SELECT name FROM sys.tables where name like '%%DB_COORD'
SET @command = ''
OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @tblname

    WHILE @@FETCH_STATUS <> -1
    BEGIN
        select @command =  @command + 'INSERT into MasterPoints(SourceID, Xcoord, Ycoord, Zcoord, PtCode, SourceTable)  SELECT UPTNUM, EAST, NORTH, ELEVATION, CODE,''' + @tblname + '''from "' + @tblname + '" '
        FETCH NEXT FROM TableCursor INTO @tblname
    END
CLOSE TableCursor
DEALLOCATE TableCursor

execute sp_executesql @command

ВЫБЕРИТЕ отдельный [SourceTable] ОТ [Манифольд]. [Dbo]. [MasterPoints]

Answer 2

Не очень понятно о проблеме. Имя столбцов этих таблиц одинаковое? Вы хотите вставить в PointTables?

Вы можете создать таблицу:

create table PointTables(
UniqueID    int identity
, Xcoord    int
, Ycoord    int
, Zcoord    int
, SourceTable   varchar(50)

После этого вы можете вставить таблицу с помощью команды sp_executesql и конкатенации.

declare @command nvarchar(max) 
select @command = 'insert into PointTables(Xcoord,YCoord,ZCoord,SourceTable)
select [Xcoord],[YCoord],[Zcoord],'''+name+''' from '+name from sys.tables where name like '%CoordDB%'
execute sp_executesql @command