我可以在临时表中插入存储过程的结果加上另一个值吗？

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.

我有一个临时表,需要存储过程的值。因此SP在临时表中插入3列,然后我想在不修改SP的情况下为每一行添加日期时间。

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

由于我每次调用SP 3次,每次使用不同的日期时间,我不能只更新整个临时表。

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

1 个解决方案

#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.

是。创建临时表,并在没有来自您的sproc的表示的添加日期列中放置getdate()的默认值约束或您需要的任何日期公式。在insert语句中明确指定列并省略日期列,它应该工作。随着行的添加,默认值约束将启动并为您填充该列。

example:

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

insert into #tmp
(c1, c2)
exec mysproc