INSERT INTO SELECT without duplicates MSSQL-CodePudding

So the thing is I have two CTEs from which I want to insert the values in a table. In the P CTE I have 50 registrations, while in the M CTE I have 8. What I want is to insert in destination table all registrations from P only once and assign the registration from M randomly.

What I wrote insert 300 rows instead of 50. Here is my code:

;WITH P
AS
(
    SELECT [ID pacient]
    FROM Pacienti
    WHERE [ID pacient] NOT IN(SELECT [ID pacient] FROM [Pacient-Medic de familie])
    GROUP BY [ID Pacient]
),
M AS
(
    SELECT FunctiiMedici.[ID medic]
    FROM FunctiiMedici
    INNER JOIN Functii
    ON Functii.[ID functie]=FunctiiMedici.[ID functie]
    WHERE Functii.Denumire like 'Medic generalist'
    GROUP BY [ID Medic]
)

INSERT INTO [Pacient-Medic de familie]([ID medic], [ID pacient])
SELECT [ID Medic], [ID Pacient] FROM M, P

CodePudding user response：

There are several possibilities.

One: change the last query to something like

INSERT INTO [Pacient-Medic de familie]([ID medic], [ID pacient])
SELECT DISTINCT [ID Medic], [ID Pacient] FROM M, P

Or you could design a query that returns only the data you need and not a cross of all entries. The database structure is not clear enough to have a guide

CodePudding user response：

INSERT INTO [Pacient-Medic de familie]([ID medic], [ID pacient])
SELECT [ID Medic], [ID Pacient] FROM M, P

This will produce a cross join, which is most likely what you're seeing. Try an explicit JOIN to reduce the results, something like :

INSERT INTO [Pacient-Medic de familie]([ID medic], [ID pacient])
SELECT     [ID Medic], [ID Pacient]
FROM       M
INNER JOIN P ON M.Key = P.Key

Your key is what should JOIN the two queries and reduce the resultset as your expecting it to