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

Question

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

[Inserting Data from Other Tables](https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16#OtherTables) — Thom A, Oct 24 '22 at 13:21
Does this answer your question? [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) — Thom A, Oct 24 '22 at 13:23
With the Insert into Select I can't find the way for adding more then one row in the DB. For sure works with only one value, but I have 8 rows to add... — Gianfranco Vrech, Oct 24 '22 at 13:31
You need to elaborate on your requirements then. Code that *attempts* to do what you want without an explanation of what that attempt is doesn't help us; especially when that syntax is clearly wrong. — Thom A, Oct 24 '22 at 13:33
try this answer https://stackoverflow.com/a/21056554/5193536 — nbk, Oct 24 '22 at 13:34
@Hogan that will be internally converted to an inner join, so that it will be no problemm only a bit more code — nbk, Oct 24 '22 at 14:03
@Hogan as i wrote alredy sql server converts it as it wants, it has no perfomance issues, so it really doesn't matter how you write it — nbk, Oct 24 '22 at 14:29
@nbk -- my point is that a select statement for each row would have performance issues. I don't think it would "merge" the statements but make 3 left joins. I guess an execution plan would let us know. — Hogan, Oct 24 '22 at 15:17
@nbk That's completely fallacious. The server will not convert multiple subqueries like that into a single join. — Charlieface, Oct 24 '22 at 21:01

Hogan · Answer 1 · 2022-10-24T14:01:11.527

0

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'

edited Oct 24 '22 at 14:01

answered Oct 24 '22 at 13:31

Hogan

69,564
10
76
117

1

A *good* answer explains what the solution does. – Thom A Oct 24 '22 at 13:32
@Larnu -- I'm looking forward to your answer then. – Hogan Oct 24 '22 at 13:34
The question is closed, @Hogan , and until the OP explains in more detail I am not happy to unhammer it. – Thom A Oct 24 '22 at 13:35
Interesting, so you don't think my answer solved the problem and showed how to query a value and add it as a column to a values list? @Larnu? – Hogan Oct 24 '22 at 13:44
Where did I say it didn't solve the problem, @Hogan ? It seems like you are reading "good" as a synonym for solved, and thus making the conclusion that my comment on how this answer could be improved means "This doesn't solve the problem." My comment ("A _good_ answer explains what the solution does.") in no way states that. – Thom A Oct 24 '22 at 13:47
@larnu -- Some answers don't require explanations. In this case I could say something like, "To have a value appear on every line you use the CROSS JOIN construct" Which is true, but does not give any additional information over showing how it is done. This question is about syntax and what is the syntax to do what the user wants. There really isn't any explanation I can see other than how SQL works syntactically. That is why I ask you to show me. You say my answer isn't good, but my guess is the OP looked and my answer and was able to solve their problem. That is a win for me. – Hogan Oct 24 '22 at 13:59
@Charlieface -- that would not work - but I did change it to use where – Hogan Oct 24 '22 at 14:01
@Hogan why would `JOIN tblPart ON tblPart.ordernr=N'555'` *not* work? – Thom A Oct 24 '22 at 14:02
@Larnu -- I think SQL Server complains about cross joins without the cross join keyword – Hogan Oct 24 '22 at 14:05
1

@Charlieface is suggesting a `(INNER) JOIN`, Hogan, not a `CROSS JOIN`. A `CROSS JOIN` *cannot* have an `ON` clause, an `INNER JOIN` *must* have one. – Thom A Oct 24 '22 at 14:06
*"Some answers don't require explanations."* You would be very surprised how many users here have no idea what a `CROSS JOIN` is or does then. – Thom A Oct 24 '22 at 14:06
"cannot have an ON clause" That is why I moved it to the where. – Hogan Oct 24 '22 at 14:07
Either way: You could just use `JOIN tblPart ON tblPart.ordernr = N'555'` like any other join, the word `INNER` is optional. `CROSS JOIN` with a `WHERE` is pretty confusing. – Charlieface Oct 24 '22 at 14:08
1

*"cannot have an ON clause"* A `CROSS JOIN` cannot, but you said an `INNER JOIN` would not work, I am asking you why you are stating an `INNER JOIN` would not work. – Thom A Oct 24 '22 at 14:08
@Larnu -- I think SQL Server complains about cross joins without the cross join keyword – Hogan Oct 24 '22 at 14:13
I have no idea what you mean by that... – Thom A Oct 24 '22 at 14:16
That makes no sense. When you do a cross-join, the syntax is simply `CROSS JOIN tablename` with no `ON`, but when you do a regular join the syntax is `[jointype] JOIN tablename ON condition`. There are no other ways of writing it. Normally, if you join a table and put a condition on it then you use `INNER JOIN` not `CROSS JOIN` – Charlieface Oct 24 '22 at 20:59

score 0 · Accepted Answer · answered Oct 24 '22 at 21:07

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.

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

2 Answers2