SQL Server Performance Forum – Threads Archive

Using DTS to create table from query results

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?

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

You can use openquery/openrowset and linked server calls to get the data from excel insert them into tables using tsql… http://support.microsoft.com/kb/321686
http://support.microsoft.com/kb/306397 MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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.

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

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.

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

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

]]>