How do I merge many similar tables?

Question

I have many tables that all have identical structure and similar table names and am looking for a way to merge a few columns from them all into a new table with two additional columns: an auto-generated integer PK, and the name of the source table. e.g.,

UniqueID SourceID, Xcoord, Ycoord, Zcoord, SourceTable

I managed to create a table containing a list of all the tables I want to use, but don't know what to do next.

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

you are getting table name by using sys.tables but what about columns join with sys.columns — mohan111, Jul 11 '15 at 03:41
How many tables do you have? When I faced a similar situation I used union operations and then inserted that query into a new table. In my case the two new columns were an identity column and a calculated column. — , Jul 11 '15 at 04:01

score 0 · Answer 1 · answered Jul 11 '15 at 06:18

0

Not very clear about the problem. Are the columns name of those tables are same? You want to insert to the PointTables?

You can create the table:

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

After that, you can insert table with help of sp_executesql command and concatenation

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

answered Jul 11 '15 at 06:18

Charlie Lukman

23
1
5

Thanks - this gets me close but only grabs the data from the first table it finds. After searching around I found mention of cursors, but dont know how to use them yet. – Land Surveyor Jul 11 '15 at 19:27
Well, actually you can do some workaround with looping. Create a table that contain 2 columns: id and tablename. Id column is autoincrement, used for looping. Looping has better performance and easier syntax than cursor. – Charlie Lukman Jul 17 '15 at 12:17
Thanks. Performance will become an issue when I start working on real data, so I will try that on the next round. – Land Surveyor Jul 17 '15 at 18:56

score 0 · Answer 2 · answered Jul 11 '15 at 21:08

The answer from Charlie Lukman was a good start but for some reason only worked on the first table. I looked at several other posts and discovered cursors which allow you to work on one line at a time using a WHILE loop to build / concatenate several INSERT INTO commands. While this works in my test of 5 tables, I am concerned about performance when I get to 100's or 1000's of tables.

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

SELECT distinct [SourceTable] FROM [Manifold].[dbo].[MasterPoints]

How do I merge many similar tables?

2 Answers2