CODEWITHSUNDEEP
sqldatabasearrays
Dan
Dan

Reputation: 1192

SQL 'arrays' for repeated use with IN comparisons

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.

Upvotes: 2

Views: 108

Answers (2)

Matthias Meid
Matthias Meid

Reputation: 12523

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

Upvotes: 1

Sigar Dave
Sigar Dave

Reputation: 2654

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

Upvotes: 0

Related Questions