Sql query question.

pankaj_daga · November 30th, 2003, 10:26 AM

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,
Pankaj

Imar · November 30th, 2003, 10:56 AM

Hi Pankaj,

I think you need to fix two things: you should leave out VALUES and you need to swap the order of the SQL statement that retrieves the records. This should work:

INSERT INTO MyTable(ExperimentID, Threshold, Energy) SELECT @newID Threshold, Energy FROM OldTable WHERE ExperimentID = @oldID

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Jeff Mason · November 30th, 2003, 11:04 AM

Subqueries are not allowed in the VALUES clause of an INSERT statement. You can use a derived table as the source of the INSERT values instead:

Code:

INSERT INTO MyTable (ExperimentID, Threshold, Energy)
    SELECT @newID, Threshold, Energy
        FROM OldTable WHERE ExperimentID = @oldID;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

pankaj_daga · November 30th, 2003, 03:04 PM

Ahhhhhh yeah, I see! Thanks a bunch guys! I really appreciate it.

Sincerely,
Pankaj