Call a stored procedure from within an INSERT INTO

So I have this stored procedure that takes in a DATETIME parameter, date. It inserts that value into another table and returns that ID.

Lets call this stored procedure getTimeID. Where DimTime is the table it inserts the DATETIME parameter into.

So I want to copy into TableB ID and TimeID, not ID and date.

I'm trying:

INSERT INTO  [TableA].dbo.Main (ID, timeID) 
SELECT  ID,
        (EXEC getTimeID date)
FROM      [TableB].dbo.Main

But I cannot get the sy开发者_开发技巧ntax on EXEC to work.

Please help.

The syntax for inserting from a stored procedure is:

INSERT INTO TheTable (col1, col2) EXEC TheStoredProcedure

It's very inflexible: the table has to match the exact column layout of the stored procedure.

One possible solution would be to store the result from the stored procedure into a table variable. For example:

declare @id int
select  @id = ID 
from    [TableB].dbo.Main

declare @t table (TimeID int)
insert @t (TimeID) exec getTimeID '2011-01-20'

declare @TimeID int
select  @TimeID = TimeID 
from    @t

insert [TableA].dbo.Main values (@ID, @TimeID) 

If you are going to want to nest your stored procedure calls or use dynamic SQL then you'll end up using temp tables instead of table variables. You'll still need to define the table ahead of time but you'll populate it like this:

INSERT INTO #t EXEC sp_executesql @cmd

The other processes that Andomar suggests for your specific scenario would work well.

If you have the ability though, I would convert that stored procedure in to a function and then just use:

INSERT INTO [TableA].dbo.Main (ID, timeID) 
SELECT ID,
       dbo.getTimeID(date)
FROM [TableB].dbo.Main