p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   Sql query question. (http://p2p.wrox.com/showthread.php?t=6701)

pankaj_daga November 30th, 2003 10:26 AM

Sql query question.
 
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



All times are GMT -4. The time now is 01:10 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.