Ok,

I have a stored proc that selects some values from a table like so:



CREATE PROCEDURE usp_InsertThisIntoThat

AS

BEGIN

@someValue int,



Select values from table where someValue = something



Next I need to insert the value(s) I get back into a new table, so I am calling another stored proc as so:



EXEC usp_Insert 1,ValueFromAboveGoesHere





END

GO



How do I get the value from the select statement into the insert statment?

I would think that it would be east to create a varible and all that, but nothing I have tried has worked.

I tried setting a value = to the select statement, but that only insets the last record.



Thanks in advance.

This can be done in 1 stored procedure.

In your INSERT stored procedure do this:

CREATE PROCEDURE foo @someValue int as

INSERT INTO table(column, column, column)
SELECT column, column, column From [table] where someValue = @someValue

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

I did try and, and it works really well for one value, but if I have more than one value it seems to only insert still one value.

Can you post the code that you tried in getting this work? Only then we can point where you go wrong or understand what needs to be altered to get it work the correct way you wanted it to.

When you say... you do this from within the procedure... See the insert statement prefixed there in bold letters. That should be enough to accomplish what you are trying to, if it is right that we understood from your post.

cheers.

_________________________
- Vijay G
Strive for Perfection

dzitam, if this query:

INSERT INTO table(column, column, column)
SELECT column, column, column From [table] where someValue = @someValue

is only inserting one value, it is (probably) becuase of your WHERE statement.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

Wow, I can't believe this isn't working at this point.

I am getting some lookup values from a table:

1 1
1 2
1 3
2 1
1 4

where value = 1

I am getting from my select all of the values.
Now, for each value I get back, I need to insert into a new table a new row.
here is the code I am using based on how I think it should be from the above listing:


INSERT INTO Emission(SID, GID, IsActive) values( 113,

SELECT LookupGas.TypeID
FROM LookupEmissionSubCategory INNER JOIN
                      GasEmissionSubCategory ON LookupEmissionSubCategory.TypeID = GasEmissionSubCategory.EmissionSubCategoryID RIGHT OUTER JOIN
                      LookupGas ON GasEmissionSubCategory.GasCategoryID = LookupGas.GasCategoryID
WHERE (LookupEmissionSubCategory.TypeID = 4), 0)



Thanks for all your help:)

hmmm that should give you an error.

INSERT INTO Emission(SID, GID, IsActive)
SELECT 113, LookupGas.TypeID, 0
FROM LookupEmissionSubCategory INNER JOIN
                      GasEmissionSubCategory ON LookupEmissionSubCategory.TypeID = GasEmissionSubCategory.EmissionSubCategoryID RIGHT OUTER JOIN
                      LookupGas ON GasEmissionSubCategory.GasCategoryID = LookupGas.GasCategoryID
WHERE (LookupEmissionSubCategory.TypeID = 4)

and, based on your example, Where value = 4 should only return 1 row.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

oh, you're right, my bad.
Suposing we change the value from 4 to 1, any insight on how to make it work, or are you just pointing out that 4 would return one value?

oh, you're right, my bad.
Suposing we change the value from 4 to 1, any insight on how to make it work, or are you just pointing out that 4 would return one value?


Update, oh wait, I see more now that I look:)
Let me try you're posted code.

I am so happy to say that you are the one:)
I would have never gotten this without you, thanks a lot,