Insert from different table without any connection

Transact-SQL
1

Hello,

i wonder if the following situation is doable in one statement.

There are two tables or selects

select ID from portfolio where show = true;
select ID from disciplines;

the combination of both selections should be inserted in another table.
So each ID from the first select should be combined with each ID in the second select and inserted in the third table.

Can this be done?

Thanks in advance

Erwin

2

Welcome

Do the values of the IDs from these tables have the same meaning?

3

No it are the ID's within that table, the primary key.

4

If I understand your question correctly then CROSS JOIN is the answer:

SELECT p.ID, d.ID
FROM portfolio p
CROSS JOIN disciplines d
WHERE p.show = true;

5

What is there are duplicate Ids how do you want to handle those?

6

Hi, tge ID's in both tables are unique (primary key) so duplicates will not apear.

Regards
Erwin

7 
Select * 
Into dbo.combined
from (
select ID from portfolio where show = true
Union all
select ID from disciplines) c

One possible way

8

How can you be sure they will be unique? Is the ID a generated identity - if so then there is no way to guarantee they are unique.

Further problem - once you have the data in this other table, how do you know that the ID value of 1234 is a portfolio vs a discipline? Since the ID value comes from 2 different tables you no longer have any way of validating the ID, nor do you have any way of relating this new table back to the original source.

What problem are you trying to solve here? What is the end goal?

9

because they are the primary key, teh end goal is as follows.

Two tables : Disciplines and Portfolio

Two selects : - select ID from disciplines -> ( f.i 1,2,3)
select ID from portfilio where show - true -> ( f.i. 10,11)

i want to add records to a third table called projectdisciplines, i want a combination of the first and second select, into a third column.
This the would be
10,1
10,2
10,3
11,1
11,2
11,3

Regards
Erwin

10

The new table will have 2 columns - one for portfolio and the other for discipline. As was pointed out earlier in the thread - you need a CROSS JOIN.