Call the stored procedure from INSERT INTO

Question

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

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

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

I'm trying to:

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

But I can't get EXEC syntax to work.

Please, help.

Answer 1

The syntax for inserting from a stored procedure is:

INSERT INTO TheTable (col1, col2) EXEC TheStoredProcedure

It is very inflexible: the table must match the exact column structure of the stored procedure.

. :

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) 

Answer 2

SQL, temp . , :

INSERT INTO #t EXEC sp_executesql @cmd

, Andomar , .

, , :

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