Combining two temporary tables with variable number of entries

Question

I have a question concerning the combination of entries of two different tables, where one of them has a variable number of entries respectively filled columns.

My input data:

I have a table1 containing a variable number of entries/columns (I only know how many columns there could be maximal), I have a table2 with a defined number of entries and I created a table3 for my results with a defined number of columns (number of columns = maximum number of columns when combining the first two tables).

My task is:

I want to take - all entries in a row from table1 - and combine it with only - four entries in a row from table2 - in one row in a new table3. But at the same time I don't know how many filled columns table1 has. So I can't define in which columns of table3 the entries of table1 should be inserted.

Example data:

-- TABLE1 and TABLE2 are my input 
-- Here I defined 8 columns for TABLE1, but it can be more or less

CREATE TABLE #t1(
    [ID] [int] identity(1,1),
    [IDBG1] [int] NULL,
    [BG1] nvarchar(max),
    [IDBG2] [int] NULL,
    [BG2] nvarchar(max),
    [IDBG3] [int] NULL,
    [BG3] nvarchar(max),
    [IDBG4] [int] NULL,
    [BG4] nvarchar(max)
    )

CREATE TABLE #t2(
    [ID] [int] identity(1,1),
    [IDBG1] [int] NULL,
    [BG1] nvarchar(max),
    [IDBG2] [int] NULL,
    [BG2] nvarchar(max)
    )

-- TABLE3 is for my results 
-- number of columns is max. number of columns of TABLE 1 plus number of columns of TABLE2 
-- here: 8 Columns for TABLE1 entries and 4 columns for TABLE2 entries

CREATE TABLE #t3(
    [ID] [int] identity(1,1),
    [IDBG1] [int] NULL,
    [BG1] nvarchar(max),
    [IDBG2] [int] NULL,
    [BG2] nvarchar(max),
    [IDBG3] [int] NULL,
    [BG3] nvarchar(max),
    [IDBG4] [int] NULL,
    [BG4] nvarchar(max),
    [IDBG5] [int] NULL,
    [BG5] nvarchar(max),
    [IDBG6] [int] NULL,
    [BG6] nvarchar(max)
    )

-- the IDBG entries are ID's and the BG's are the corresponding names
-- for example

INSERT INTO #t1 (IDBG1, BG1, IDBG2, BG2, IDBG3, BG3, IDBG4, BG4)
VALUES (102, 'BS', 302, 'SL', 345, 'AS', 75, 'LT')

INSERT INTO #t2 (IDBG1, BG1, IDBG2, BG2)
VALUES (900, 'SM', 789, 'CS')

SELECT * FROM #t1
SELECT * FROM #t2
SELECT * FROM #t3

Is that possible? Because I only know, that the columns which should be filled have to be specified before!

And there's no relation between t1 and t2? You just want t2's columns to the right of t1's columns? What's expected to happen if there are several rows in the tables? And, even worse, different number of rows? — jarlh, Nov 20 '15 at 12:56
No, there is no relation. I want to combine all rows in table1 with all rows in table2 so that in the end I have [(nr. of rows in table1) times (nr. of rows in table2)] different solutions. — T.R., Nov 20 '15 at 13:11

score 0 · Answer 1 · edited May 23 '17 at 11:52

What you want to do is called a CROSS JOIN:

INSERT INTO #t1 (IDBG1, BG1, IDBG2, BG2, IDBG3, BG3, IDBG4, BG4)
VALUES (102, 'BS', 302, 'SL', 345, 'AS', 75, 'LT')
    , (103, 'BS', 302, 'SL', 345, 'AS', 75, 'LT')

INSERT INTO #t2 (IDBG1, BG1, IDBG2, BG2)
VALUES (900, 'SM', 789, 'CS')
    , (901, 'SM', 789, 'CS')

INSERT INTO #t3(IDBG1, BG1, IDBG2, BG2, IDBG3, BG3, IDBG4, BG4, IDBG5, BG5, IDBG6, BG6) 
SELECT t1.IDBG1, t1.BG1, t1.IDBG2, t1.BG2, t1.IDBG3, t1.BG3, t1.IDBG4, t1.BG4 
    , [IDBG5] = t2.IDBG1, [BG5] = t2.BG1, [IDBG6] = t2.IDBG2, [BG6] = t2.BG2
FROM #t1 t1 CROSS JOIN #t2 t2

SELECT * FROM #t1
SELECT * FROM #t2
SELECT * FROM #t3

Output:

ID  IDBG1   BG1 IDBG2   BG2 IDBG3   BG3 IDBG4   BG4 IDBG5   BG5 IDBG6   BG6
1   102     BS  302     SL  345     AS  75      LT  900     SM  789     CS
2   103     BS  302     SL  345     AS  75      LT  900     SM  789     CS
3   102     BS  302     SL  345     AS  75      LT  901     SM  789     CS
4   103     BS  302     SL  345     AS  75      LT  901     SM  789     CS

See:

CROSS JOIN on Technet
CROSS JOIN vs INNER JOIN on stackoverflow

Now, if you don't know what is in t1, you need to write some dynamic SQL.

This query gets:

t1 colums (except ID) in @col_select
1st 4 columns from #t2 (except ID) in @col_select
first N columns from #t3 (except ID) in @col_insert with N = count from t1 + 4 (for t2)

Query:

declare @t1_id bigint = OBJECT_ID('tempdb.dbo.#t1');
declare @t2_id bigint = OBJECT_ID('tempdb.dbo.#t2');
declare @t3_id bigint = OBJECT_ID('tempdb.dbo.#t3');
declare @col_select nvarchar(max),  @col_insert nvarchar(max), @sql nvarchar(max);

-- Get #t1 columns
Set @col_select = STUFF((
        SELECT ', ' + 't1.' + QUOTENAME(name) 
        FROM  tempdb.sys.columns 
        WHERE OBJECT_ID = @t1_id AND name not like 'ID'
        ORDER BY column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'');

-- Add first 4 columns from #t2
Set @col_select = @col_select + ',' + STUFF((
        SELECT TOP(4) ', ' + 't2.' + QUOTENAME(name) 
        FROM  tempdb.sys.columns 
        WHERE OBJECT_ID = @t2_id AND name not like 'ID'
        ORDER BY column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'');

-- Get first n columns from #t3 (n = t1 count + 4)
Set @col_insert = STUFF((
        SELECT TOP(SELECT COUNT(*)-1+4 FROM  tempdb.sys.columns WHERE OBJECT_ID = @t1_id) ', ' + QUOTENAME(name) 
        FROM  tempdb.sys.columns 
        WHERE OBJECT_ID = @t3_id AND name not like 'ID'
        ORDER BY column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'');

SET @sql = '
    INSERT INTO #t3(' + @col_insert + ') 
    SELECT ' + @col_select + '
    FROM #t1 t1 CROSS JOIN #t2 t2
';

EXEC sp_executesql @sql;

With your sample it gets:

@col_select = t1.[IDBG1], t1.[BG1], t1.[IDBG2], t1.[BG2], t1.[IDBG3], t1.[BG3], t1.[IDBG4], t1.[BG4], t2.[IDBG1], t2.[BG1], t2.[IDBG2], t2.[BG2]
@col_select = t1.[IDBG1], t1.[BG1], t1.[IDBG2], t1.[BG2], t1.[IDBG3], t1.[BG3], t1.[IDBG4], t1.[BG4]
@col_insert = [IDBG1], [BG1], [IDBG2], [BG2], [IDBG3], [BG3], [IDBG4], [BG4], [IDBG5], [BG5], [IDBG6], [BG6]

Combining two temporary tables with variable number of entries

1 Answers1