SQL VB.net insert using select AND values

Question

New to using SQL and I am having trouble accomplishing something that I figured would be simple.

I am trying to create and INSERT that will insert the MAX() value from the first column in one table to the first column in another table while the rest of the columns will be filled with parameters.

I have tried switching my code around to see if I just had the syntax wrong but I've had no luck and I'm not even sure what I'm trying to do is possible (at least in a single INSERT).

Here is what I have at the moment:

INSERT INTO [Table2] VALUES(SELECT(Number FROM [Table1] WHERE Max(Number)), @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)

try creating the script in Sql Server Management Studio (SSMS) first and then work it into your code. — JeffO
– JeffO, Commented Apr 6, 2011 at 14:04

mariocase84 · Accepted Answer · 2011-04-06 13:57:50Z

1

the select statement must be enclosed in parentheses and I recommend you always put the list of columns in the insert statement

Insert into [MyTable] (Max,Value1,Value2,Value3) values((select Max(Number) from Table1),@p1,@p2,@p3)

answered Apr 6, 2011 at 13:57

mariocase84

4362 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RHOMBUS Over a year ago

Ahh, damn parentheses. Thanks for your help, this worked perfectly.

il_guru · Accepted Answer · 2011-04-06 13:57:48Z

0

First: The SELECT MAX statement is wrong, the correct syntax is:

SELECT Max(Number) From [Table1]

Second: the INSERT syntax is wrong. To use a subquery you have to select values for every field in the destination table, while if you use the VALUES keyword you have to specify the field to insert the value in.

So your query have to became something like this:

INSERT INTO [Table2] 
SELECT Max(Number), @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10 From [Table1]

OR (i'm using lesser parameter for practical reason)

INSERT INTO [Table2] (field1,field2,field3)
VALUES
(@MAX, @p1, @p2)

where @MAX is a variable containing the result of

SELECT Max(Number) From [Table1]

answered Apr 6, 2011 at 13:57

il_guru

8,5483 gold badges46 silver badges52 bronze badges

Collectives™ on Stack Overflow

SQL VB.net insert using select AND values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related