SQL Server - Efficiently storing the results of a query in a tracking table

Question

I have been asked to keep track of how many times each item comes up within the results of a particular query. My thought is to just store the result of a query into a tracking table and then spit the results back to the caller. I am wondering what the most efficient method of storing these results would be since the result set could include up to 1000 records.

My plan is to pull the query results into a temp table and insert those results into the tracking table then return the temp table as the result of the SPROC. Something like this:

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 the bulk result set into a tracking table?

Answer 1

You don't need the @Results table.

Directly inserting and selecting from TableA does the job and will most likely be the most efficient way.

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

Answer 2

If you are using Sql 2008, you could return the rows as XML. Then you would just have 1 row to insert in your tracking table.