SQL Query for insert many values in a table and take only a value from another table-CodePudding

I'm looking for insert many values in a table and take the ID refernce from another table. I have tried diffent ways, and finaly I have found this that works.

INSERT INTO tblUserFreeProperty (id, identname, val, pos)
VALUES ((SELECT id FROM tblpart where tblPart.ordernr=N'3CFSU05'),N'DSR_Mag.G', N'??_??@False', 1),
       ((SELECT id FROM tblpart where tblPart.ordernr=N'3CFSU05'),N'DSR_Mag.Qta_C', N'??_??@0', 2),
       ((SELECT id FROM tblpart where tblPart.ordernr=N'3CFSU05'),N'DSR_Mag.Qta_M', N'??_??@0', 3),
       ((SELECT id FROM tblpart where tblPart.ordernr=N'3CFSU05'),N'DSR_Mag.UbicM', N'??_??@No', 4),
       ((SELECT id FROM tblpart where tblPart.ordernr=N'3CFSU05'),N'DSR_Mag.UbicS', N'??_??@', 5),
       ((SELECT id FROM tblpart where tblPart.ordernr=N'3CFSU05'),N'DSR_Mag.UbicP', N'??_??@', 6),
       ((SELECT id FROM tblpart where tblPart.ordernr=N'3CFSU05'),N'DSR_Mag.UbicC', N'??_??@', 7);

This works, but I'm looking for a "easy query" because I need to write the command from Visual Studio

CodePudding user response：

INSERT tblUserFreeProperty (id, identname, val, pos)
    SELECT tblpart.id, X.A, X.B, X.C)
    FROM ( 
      VALUES (
        (N'DSR_Mag.G0', N'??_??@True', 1),
        (N'DSR_Mag.G1', N'??_??@True', 2),
        (N'DSR_Mag.G2', N'??_??@False', 3);
      ) 
    ) X(A,B,C)
    CROSS JOIN tblPart
    WHERE tblPart.ordernr=N'555'

CodePudding user response：

The link I noted earlier should have sufficed to explain the correct syntax.

Insert into ... values ( SELECT ... FROM ... )

But seeing as there has been much misinformation on this post, I will show how you should do it.

INSERT INTO tblUserFreeProperty (id, identname, val, pos)
SELECT p.id, v.identname, v.val, v.pos
FROM (VALUES
       (N'DSR_Mag.G', N'??_??@False', 1),
       (N'DSR_Mag.Qta_C', N'??_??@0', 2),
       (N'DSR_Mag.Qta_M', N'??_??@0', 3),
       (N'DSR_Mag.UbicM', N'??_??@No', 4),
       (N'DSR_Mag.UbicS', N'??_??@', 5),
       (N'DSR_Mag.UbicP', N'??_??@', 6),
       (N'DSR_Mag.UbicC', N'??_??@', 7)
) AS v(identname, val, pos)
JOIN tblpart p ON p.ordernr = N'3CFSU05';

Note the use of a standard JOIN clause, there are no subqueries. Note also good use of short, meaningful table aliases.

As far as the VALUES table constructor goes, it can also be replaced with a temp table, or table variable, or Table Valued parameter. Or indeed another table.

Side note: I don't know what you are storing in those columns, but it appears you have multiple pieces of info in each. Do not do this. Store each atomic value in its own column.