INSERT INTO + SELECT

Question

Hi
I'm trying to insert a new row in a tables using some fixed values and 2 values from another table depending on a condition.
Here is my example:

INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
VALUES (@Region, @Alarm, 
SELECT dbo.Contacts.[ID] WHERE dbo.Contacts.[Name]=@Name, 
SELECT dbo.Contacts.[ID] WHERE dbo.Contacts.[Name]=@Name)

I get the following error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.

Can anyone help? Thank you in advance.

Answer 1

Try:

INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
SELECT @Region, @Alarm, 
C.[ID], C.ID from dbo.Contacts C WHERE C.[Name]=@Name

In other words, you need just one select statement and include your constant values into it.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi Naomi

Thank you for your answer.

I can see that I typed wrong, what I meant was this:

INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
VALUES (@Region, @Alarm, 
SELECT dbo.Contacts.[ID] WHERE dbo.Contacts.[Name]=@Name1, 
SELECT dbo.Contacts.[ID] WHERE dbo.Contacts.[Name]=@Name2)

There should be the possibility to have 2 different Id's in Contact1Id and Contact2Id

If I try your suggestion, and add the possibility to have 2 different Id's, I get incorrect syntax near 'AND' on line 3:

INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
SELECT @Region, @Alarm, Con1.[ID], Con2.[ID]
FROM dbo.Contacts AS Con1 AND dbo.Contacts AS Con2
WHERE Con1.[Name]=@Name1 AND Con2.[Name]=@Name2

I have also tried with ',' instead, and got same result.

Answer 3

I got it, thanks to you Naomi

Here is the solution:

INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
SELECT @Region, @Alarm, Con1.[ID], Con2.[ID]
FROM dbo.Contacts Con1, dbo.Contacts Con2
WHERE Con1.[Name]=@Name1 AND Con2.[Name]=@Name2

Answer 4

In this case you can:

declare @SecondContactID int; 
select @SecondContactId = ID from dbo.Contacts ;
where Name = @Name2;
INSERT INTO dbo.AlarmContacts ([Region], [Alarm], [Contact1Id], [Contact2Id]) 
SELECT @Region, @Alarm, Con1.[ID], @SecondContactID FROM dbo.Contacts Con1
WHERE Con1.[Name]=@Name1; 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

Thank you for your help Naomi N, it is appreciated :)

Answer 6

Thank you for this reply :)

Maybe I'm a little tired, I get an incorrect syntax near the keyword select

(= select ID from dbo.Contacts).

But it doesn't matter now, it is solved :)

Answer 7

I fixed that code, sorry for the typo. There should not have been SELECT after =

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog