SQL 'arrays' for repeated use with IN comparisons

Question

It's entirely possible to do a SELECT statement like so:

SELECT *
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);

However, is it possible to create a variable which stores that 'array' (10000, ...) for repeated use in multiple statements like so?

SELECT *
FROM orders
WHERE order_id in @IDarray;

Apologies if this is a painfully simple question - we've all gotta ask them once!

Edit: Hmm, perhaps I should clarify. In my exact situation, I have a load of IDs (let's use the array above as an example) that are hard coded but might change.

These should be re-usable for multiple INSERT statements, so that we can insert things into multiple tables for each of the IDs. Two such end results might be:

INSERT INTO table1 VALUES (10000, 1, 2, 3);
INSERT INTO table1 VALUES (10001, 1, 2, 3);
INSERT INTO table1 VALUES (10003, 1, 2, 3);
INSERT INTO table1 VALUES (10005, 1, 2, 3);

INSERT INTO table2 VALUES (10000, a, b, c);
INSERT INTO table2 VALUES (10001, a, b, c);
INSERT INTO table2 VALUES (10003, a, b, c);
INSERT INTO table2 VALUES (10005, a, b, c);

Obviously here being able to specify the array saves room and also allows it to be changed in one location instead of the INSERTs having to be modified.

If SQL Server see the canonical Arrays and Lists in SQL Server — Alex K.
– Alex K., Commented May 16, 2013 at 9:52
Which DBMS are you using? Postgres directly supports that: where order_id = ANY (ARRAY[1,2,3]) — user330315
– user330315, Commented May 16, 2013 at 11:26

Matthias Meid · Accepted Answer · 2013-05-16 14:10:29Z

1

With Microsoft SQL Server you could use an table variable:

CREATE TABLE @IDTable(id INT PRIMARY KEY);
-- insert IDs into table

SELECT *
FROM orders o
INNER JOIN @IDTable i ON i.id = o.order_id;

INSERT INTO table2
SELECT id, 1, 2, 3
  FROM @IDTable

INSERT INTO table2
SELECT id, 'a', 'b', 'c'
  FROM @IDTable

edited May 16, 2013 at 14:10

answered May 16, 2013 at 9:51

Matthias Meid

12.5k7 gold badges48 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Damien_The_Unbeliever Over a year ago

Characterizing a table variable as in "in-memory" table is close to incorrect. There's no guarantee that a table variable will be held in memory, any more than there's a guarantee that a temp table will be spilled to disk.

Matthias Meid Over a year ago

@Damien_The_Unbeliever Thanks, I did not know this by now. I edited my answer accordingly.

Dan Over a year ago

I clarified the original question - perhaps that might give more information :)

Matthias Meid Over a year ago

@Dan Are a, b and c hard-coded values that do not change from INSERT to INSERT? I edited my question, but frankly I'm not sure how much the edit tackes your issue...

Dan Over a year ago

a, b and c are also in variables, and might be obtained from a SELECT or be hard-coded. But they're the same for all of those INSERTs, i.e. for each of the insert blocks above the only thing that differs per row is the ID from the original array.

|

Sigar Dave · Accepted Answer · 2013-05-16 10:01:44Z

0

With the use of Declare table variable you can achieve what you want to do.

For example :

Declare  @tbl table(orderID int,Orders varchar(max));

insert into @tbl
SELECT * FROM orders WHERE order_id in (10000, 10001, 10003, 10005);

Select orderID ,Orders from @tbl

answered May 16, 2013 at 10:01

Sigar Dave

2,6481 gold badge23 silver badges45 bronze badges

Collectives™ on Stack Overflow

SQL 'arrays' for repeated use with IN comparisons

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related