Clever Geek Handbook

SQL Server - Efficiently Store Query Results in a Watch Table

I was asked to track how many times each item appears in the results of a particular query. My thought is to simply store the query result in a watch table and then return the results back to the caller. I am wondering what the most efficient method of storing these results would be, since a result set can contain up to 1000 records.

My plan is to dump the query results into a temp table and insert those results into a watch table, then return the temp table as a SPROC result. Something like that:

DECLARE @QueryTime datetime
SET @QueryTime = GETDATE()

DECLARE @Results TABLE (X nvarchar(255), Y nvarchar(255))

INSERT INTO @Results
SELECT X,Y FROM TableA

INSERT INTO TableB
SELECT X, @QueryTime FROM @Results

SELECT X, Y FROM @Results

Does anyone have a more efficient way to post an array of results to a tracking table?

+2


source to share


2 answers


You don't need the @Results table.

Direct insertion and selection from TableA does the job and is likely to be the most efficient way.



DECLARE @QueryTime DATETIME
SET @QueryTime = GetDate()
INSERT INTO TableB
SELECT X, @QueryTime FROM TableA
SELECT X, Y FROM @TableA
+2


source


If you are using Sql 2008 you can return strings as XML. Then you only have one row to insert into the tracking table.



+1


source






More articles:


All Articles