Large datasets to match up

Transact-SQL
1

our database at work doesn't allow for temp tables.

I have a sql query which outputs around 3600 rows with single column, all numeric numbers (1234567, 1234568, 1234569) etc. nearly 3600 which is sent to me, then I compare the same 3600+ rows of data with another table, which eliminates most of those rows and gives me ard 300 rows of final data.

now I copy the same 300 rows into another SQL batch load script to get my work complete.

is there any solution to find a substitute for temporary tables when we have a large dataset to match up, so that we do not have to do WHERE () OR () OR () OR ()?

2

If you get a list of numbers from someone and have to run it against a different system, you are limited by what you can do. You can export the results/import to the other systems but it sounds like you are prevented from doing that.

If the only option is to run SQL, the below example can give you an idea of how to try an alternative.

Just copy/paste the values you receive then JOIN to compare.

;WITH cteMyValues As
(
	SELECT DISTINCT LTRIM(m.n.value('.[1]','varchar(MAX)')) as MyColumnName
	  FROM 
		(
			SELECT CAST('<XMLRoot><RowData>' + REPLACE(t.val,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
			  FROM 
				(
					/* 
						Replace this with list of values you want to compare. 
						Make sure you separate the values with commas 
					*/
					SELECT '1, 1, 1, 2, 5, 1, 6'
				) AS t(val)
		) dt
    CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
)

SELECT *
  FROM cteMyValues As myVal
  -- JOIN to your table to compare values
  -- JOIN CompareTable As ct ON myVal.MyColumnName = ct.CompareColumnValue
3

You can use a table variable. The scope of the table variable is different from that of a temp table. Table variable goes out of scope when the batch terminates. So whether or not that will work in your case is dependent on whether you are running the query that generates the 300 Ids in the same batch as where you will be consuming those.

If you want to know all the gory details about how table variables differ from temp tables, see Martin Smith's response here.