sql server - How do I merge many similar tables? -

i have many tables have identical structure , similar table names , looking way merge few columns them new table 2 additional columns: auto-generated integer pk, , name of source table. e.g.,

uniqueid sourceid, xcoord, ycoord, zcoord, sourcetable

i managed create table containing list of tables want use, don't know next.

select [name] pointtables [surveys].[sys].[tables] [name] '%coorddb' 

not clear problem. columns name of tables same? want insert pointtables?

you can create table:

create table pointtables( uniqueid    int identity , xcoord    int , ycoord    int , zcoord    int , sourcetable   varchar(50) 

after that, can insert table of sp_executesql command , concatenation

declare @command nvarchar(max)  select @command = 'insert pointtables(xcoord,ycoord,zcoord,sourcetable) select [xcoord],[ycoord],[zcoord],'''+name+''' '+name sys.tables name '%coorddb%' execute sp_executesql @command