Can I insert into a temp table the result of a stored procedure plus another value?

Question

I have a temp table that needs the values of a Stored procedure. So the SP inserts 3 columns into the temp table, then I want to add a datetime to every row without modifying the SP.

Since I call 3 times the SP each with a different datetime, I can't just update the whole temp table.

Any Suggestions?

DECLARE @temp TABLE ( id INT IDENTITY(1,1), Name VARCHAR(150), Address VARCHAR(25), Date DATETIME )

WHILE (@count>=@daysBack)
BEGIN
    SET @date=DATEADD(dd, @count, GETDATE())
    INSERT INTO @temp (Name,Address)
        EXEC[dbo].StoredProc@date

--I Want to check for Null and insert the date there
    Update @temp SET Date=@date WHERE Date='' 

    SET @count=@count-1

Answer 1

Yes. Create the temp table and in the added date column that does not have representation from your sproc put a default value constraint of getdate() or whatever date formula you need. In the insert statement specify the columns explicitly and omit the date column and it should work. As the rows are added the default value constraint will kick in and fill that column for you.

example:

create table #tmp (c1 int, c2 int, dt datetime default(getdate()) )

insert into #tmp
(c1, c2)
exec mysproc

Answer 2

Any way to do this without manually creaiting the table first? Often times a proc is a combination of several tables and it can get quite messy to try and recreate this.