SQL Server Performance

Copying data from a temp table in a function to a real table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by bpage00, Sep 20, 2007.

  1. bpage00 New Member

    I have a current function in 2005 that starts off with the code below. In the end, it returns this "temp table" called @UVSummary
    so that Reporting Services can access and display it when needed. Now they actuall want this data to be kept in a table. So how
    do I call a temp table from another stored proc and insert this stuff?
    my example (what I think)
    INSERT INTO dbo.mytable
    SELECT (my columns)
    FROM uf_my function - ?????
    where do I go from here? Can someone piece together some answers for me? D
    Do I call @UVSummary? Or Each individual column from the temp table? Can I just copy the entire @UVSummary
    table somehow? Everything will stay the same as far as columns. So it can be slammed right in.
    please help!!!set ANSI_NULLS ON
    set
    QUOTED_IDENTIFIER ONgo
    ALTER Function [dbo].[uf_UsageVariance](@LastPriceStoreIDList
    varchar(100),@StoreIDList
    varchar(8000),@RegionIDList
    varchar(8000),@StartDate
    datetime,@EndDate
    datetime,@InventoryPeriod
    int,@BIGID
    int)
    Returns@UVSummary
    Table
    ( MasterItemID int PRIMARY KEY,ItemName
    varchar(100),InvoiceItemId
    int,OpeningInvoiceItemId
    int,VendorItemId
    int,UnitOfMeasureID
    int,UOM
    varchar(10),UnitPrice
    money,TheoreticalUsage
    decimal(18,2),Opening
    decimal(18,2),OpeningDollarValue
    decimal (18,2),OpeningPrice
    money,Closing
    decimal(18,2),ClosingDollarValue
    decimal (18,2),ClosingPrice
    money,Purchases
    decimal(18,2),PurchaseDollarValue
    decimal(18,2),TransfersIn
    decimal(18,2),TransferInDollarValue
    decimal(18,2),TransfersOut
    decimal(18,2),TransferOutDollarValue
    decimal(18,2),ActualUsage
    decimal(18,2),Variance
    decimal(18,2),VariancePercent
    decimal(18,4),TheoreticalUsageCost
    money,ActualUsageCost
    money,VarianceCost
    money,VarianceCostPercent
    decimal(18,4),GrossSales
    money,AverageUnitPrice
    money,Yield
    decimal(18,2),LastUnitPrice
    money)
  2. ndinakar Member

    didnt read the entire post bot did a "parameter sniff" and guessing you would need to
    INSERT INTO @Temp
    SELECT * FROM dbo.myfunction

Share This Page