Sql query question

[xargon]

Hi everyone,

I am trying to do a select sub-query, but for some reason it is not working out.

I have the following stored procedure:

CREATE MyProcedure
@newID int,
@oldID int
AS

INSERT INTO MyTable(ExperimentID, Threshold, Energy) VALUES(@newID, Select Threshold, Energy from OldTable where ExperimentID = @oldID)

This mixing of a given value with a subquery does not seem to work. However, there must be a mechanism to do this. Does anyone has some experience with something like this and can maybe help me.

Thanks a lot.

Cheers,

Xargon

[raghupathys]

you can use a sub query only in the search clause or in the from clause. you cannot use it to substitute the values in an update or insert statement.

so what you will have to do is select the values into some local variables and then use them in your insert statement.

[mr_nisse]

Well, according to MSDN, a select subquery is OK...

USE pubs
INSERT INTO MyBooks
SELECT title_id, title, type
FROM titles
WHERE type = 'mod_cook'

so, it should be possible.

maybe something like

INSERT INTO MyTable(ExperimentID, Threshold, Energy) VALUES(@newID, (Select Threshold, Energy from OldTable where ExperimentID = @oldID))

Hope I understood your question OK...

[antares686]

CREATE MyProcedure
@newID int,
@oldID int
AS

INSERT INTO MyTable(ExperimentID, Threshold, Energy) VALUES(@newID, Select Threshold, Energy from OldTable where ExperimentID = @oldID)

The key is that the values clause does not handle a select subquery, however a noted you can replace the values cluase with a select query. With that in mind to do what you want you have to use the select logic as note INSERT .... SELECT ...


CREATE MyProcedure
@newID int,
@oldID int
AS

INSERT INTO MyTable (ExperimentID, Threshold, Energy) Select @newID, Threshold, Energy from OldTable where ExperimentID = @oldID

That follows the proper logic and will allow you to do what you are needing. Note however if ExperimentID is UNIQUE and the select may potentially return more than one value you have to add TOP 1 to the logic of the select or some other controller to make sure you get what you are after.

[xargon]

Cool guys :-)

Thank you all very much :-) This worked :-)

Pankaj