none
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.

    Sunday, March 03, 2013 3:13 PM

Answers

  • 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

    • Marked as answer by quarck2 Sunday, March 03, 2013 4:07 PM
    Sunday, March 03, 2013 3:23 PM

All replies

  • 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

    • Marked as answer by quarck2 Sunday, March 03, 2013 4:07 PM
    Sunday, March 03, 2013 3:23 PM
  • 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.

    Sunday, March 03, 2013 3:50 PM
  • 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

    Sunday, March 03, 2013 4:02 PM
  • 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


    Sunday, March 03, 2013 4:15 PM
  • Thank you for your help Naomi N, it is appreciated :)
    Thursday, March 07, 2013 3:57 PM
  • 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 :)

    Thursday, March 07, 2013 4:49 PM
  • 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

    Thursday, March 07, 2013 4:52 PM