SQL Server Performance

Using DTS to create table from query results

Discussion in 'SQL Server DTS-Related Questions' started by mcfly1204, May 21, 2007.

  1. mcfly1204 New Member

    I am trying to use DTS to create a table from a query, but I would like to run this DTS weekly to overwrite the existing table. I can run the query through the DTS and create an Excel sheet without a problem, but when I change the DTS to insert into a table, the table is empty. I do not know if I should look to something besides DTS to do this.

    To clarify, this is what I am looking to accomplish.

    I currently use intermediate .xls files and use a custom sort to get the results I need. The issue is that I have a different .xls file for every year, given an .xls file can only hold 65,000 results or so. I wanted to create a new table to run a simple query as opposed to running a query with multiple joins everytime. The new table only needs to hold records from the last 36 months, so I would like to repopulate on a monthly basis. Any thoughts?
  2. twoboats New Member

    Schedule a sql job job to run a stored procedure that inserts into the table.

    As a first step, the stored procedure could delete the data from the table that you no longer need.

    http://www.tradeco.co.uk
  3. MohammedU New Member

  4. mcfly1204 New Member

    I would prefer to look into creating a store procedure to keep this process as automated as possible. Stored procedures are fairly new to me, if someone has a suggestion for a reference or tutorial that could get me off to a good start I would appreciate it. Posting an example would also help.
  5. twoboats New Member

    CREATE PROCEDURE my_procedure
    AS
    -- your code goes here
    GO

    -- =============================================
    -- example to execute the store procedure
    -- =============================================
    EXECUTE my_procedure
    GO

    ---------------------------------------------------------------------------------------------
    -- example from northwind database

    CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
    AS
    SELECT ProductName, Total=SUM(Quantity)
    FROM Products P, [Order Details] OD, Orders O, Customers C
    WHERE C.CustomerID = @CustomerID
    AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
    GROUP BY ProductName

    GO

    Hope this helps



    http://www.tradeco.co.uk
  6. MohammedU New Member

    http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=49


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. mcfly1204 New Member

    Thank you for the link Mohammed, I don't understand the purpose of the variable in a sotred procedure. I understand that it is a place holder for inputs, but I don't really know how that translates to my sp. I'll post what I have thus far to see if someone has a suggestion.

    CREATE PROCEDURE sp_ImprintPhrase

    AS

    INSERT INTO IMPRINTPHRASE_QUERY
    (
    CustID,
    CustName,
    TranNoRel,
    CustPONo,
    ImprintPhrase,
    CloseDate,
    ItemID
    )


    SELECT mas500_app.dbo.tarCustomer.CustID, mas500_app.dbo.tarCustomer.CustName, mas500_app.dbo.tsoSalesOrder.TranNoRel, mas500_app.dbo.tsoSalesOrder.CustPONo, mas500_app.dbo.tsoSalesOrder.CloseDate,
    mas500_app.dbo.tsoSalesOrder_SLXPSG.ImprintPhrase, mas500_app.dbo.timItem.ItemID

    FROM mas500_app.dbo.tarCustomer RIGHT OUTER JOIN
    mas500_app.dbo.tsoSalesOrder INNER JOIN
    mas500_app.dbo.tsoSOLine ON mas500_app.dbo.tsoSalesOrder.SOKey = mas500_app.dbo.tsoSOLine.SOKey ON mas500_app.dbo.tarCustomer.CustKey = mas500_app.dbo.tsoSalesOrder.CustKey LEFT OUTER JOIN
    mas500_app.dbo.tsoSalesOrder_SLXPSG ON mas500_app.dbo.tsoSalesOrder.SOKey = mas500_app.dbo.tsoSalesOrder_SLXPSG.SOKey LEFT OUTER JOIN
    mas500_app.dbo.timItem ON mas500_app.dbo.tsoSOLine.ItemKey = mas500_app.dbo.timItem.ItemKey

    WHERE (mas500_app.dbo.tsoSalesOrder.CloseDate> CONVERT(datetime, '20041231') OR
    mas500_app.dbo.tsoSalesOrder.CloseDate IS NULL) AND (NOT (mas500_app.dbo.tsoSalesOrder.CustPONo LIKE '[coop*]')) AND (mas500_app.dbo.tsoSOLine.SOLineNo = 1) AND
    (mas500_app.dbo.tsoSalesOrder.CompanyID = 'qmc')

    ORDER BY mas500_app.dbo.tarCustomer.CustID, mas500_app.dbo.tsoSalesOrder.CloseDate

    GO
  8. twoboats New Member

    Have you done any coding in other programming languages? If so, the variables are the same.

    E.g You could calculate a value and store it in the variable @val

    set @val = (select min(AC3)*2 from another_tab)

    Then you could use that value in your select statement e.g.

    Insert into my_tab (C1, C2, C3)

    select AC1, AC2, AC3 from another_tab where AC3 <= @val#


    There's nothing to say you must use any variables - they're there if you need them. Looks like you don't...



    http://www.tradeco.co.uk

Share This Page