[RESOLVED] INSERT with Multiple Queries

[jsc0624]

Hi Guys,

I don't know how to write this in proper SQL Commands:

INSERT INTO tblTable1
(
   Field1,
   Field2,
   Field3
)
VALUES
(
   @Field1,
   (SELECT FieldID FROM tblTable2 WHERE FieldName = @FieldName),
   (SELECT FieldID FROM tblTable3 WHERE FieldName = @FieldName)
)

That code doesn't work with my Stored Procedure.

Thanks in advance

[dee-u]

Why not something like this?

    DECLARE @value1 nvarchar(200)
    DECLARE @value2 nvarchar(200)
    SET @value1 = SELECT FieldID FROM tblTable2 WHERE FieldName = @FieldName
    SET @value2 = SELECT FieldID FROM tblTable3 WHERE FieldName = @FieldName
    INSERT INTO tblTable1
    (
       Field1,
       Field2,
       Field3
    )
    Values
    (
       @Field1,
       @value1,
       @value2)
    )

[jsc0624]

when I tried to use this code:

SET @varNames = SELECT NameID FROM tblNames WHERE Name = @Name

This message appears at the bottom.

Msg 156, Level 15, State 1, Procedure prPODataEntry, Line 47
Incorrect syntax near the keyword 'SELECT'.

[NeedSomeAnswers]

change it to -

SELECT @varNames = NameID FROM tblNames WHERE Name = @Name

[jsc0624]

Thanks guys,
and also I have learned that we can also write it like this:

SET @varNames = (SELECT NameID FROM tblNames WHERE Name = @Name)

[erickwidya]

or if u want to insert multiple value to single field, u can use something like this

INSERT INTO tblTable1
(
   Field1
)
   SELECT @Field1
   UNION
   SELECT FieldID FROM tblTable2 WHERE FieldName = @FieldName
   UNION
   SELECT FieldID FROM tblTable3 WHERE FieldName = @FieldName