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.

  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
    -- your code goes here

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

    -- example from northwind database

    CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
    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


    Hope this helps

  6. MohammedU New Member



    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



    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

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


Share This Page