How can I store a long list in a SQL table?

Question

I have a very long line of random numbers, e.g. 234,364...,632. I want to insert this line into a SQL temp table so that I can use it in an IN (SELECT * FROM #MYTABLE) statement of various queries. How can I do that? Apparently there is not an easy way to insert this list into a column in a table. I was thinking to insert it as a row and then pivot the table. Any solution please?

Please _don't_ do this; store each number in a separate record instead. — Tim Biegeleisen, Feb 18 '21 at 12:30
There is not such thing as a "list" or "array" in tsql. Do you have a string (scalar value - perhaps a local variable or a paramter) containing numbers separated by commas? — SMor, Feb 18 '21 at 12:37
You're sitting there staring at a relational data storage system and trying to figure out how to not use it as relational storage. Why? Do what @TimBiegeleisen says. — Grant Fritchey, Feb 18 '21 at 13:26
OK but what is your proposal actually? The numbers in the line change and I want to easily update the list. I then want to use the updated list in various statements within the same query. What is the best approach? Obviously, I cannot insert each number into a table, this will create a 4,000 lines query as my list has 4,000 numbers. — user13654090, Feb 18 '21 at 14:25
Does this answer your question? ['Must Declare the Scalar Variable' Error When Passing a Table-Valued Parameter to a Parameterized SQL Statement](https://stackoverflow.com/questions/66065761/must-declare-the-scalar-variable-error-when-passing-a-table-valued-parameter-t) — Charlieface, Feb 18 '21 at 14:30
Having already supplied an answer, I'm reluctant to mark this as a duplicate, but many similar questions have been asked: e.g. https://stackoverflow.com/questions/10914576/t-sql-split-string — Steve Lovell, Feb 18 '21 at 14:41
`I cannot insert each number into a table, this will create a 4,000 lines query` What's the problem with a 4000 line query? That's actualy what table-valued-parameters do under the hood. — Alejandro, Feb 18 '21 at 14:41
There's no problem with long insert statements like `INSERT INTO #mytable VALUES (234),(364),(...),(632)` to create 4000 rows. — MatBailie, Feb 18 '21 at 16:19
@Alejandro TVPs most certainly [do not do 4000 queries](https://stackoverflow.com/questions/56744315/populate-a-table-valued-parameter-in-ado-net-with-one-insert-statement-for-multi), they use a special Bulk Insert parameter binding via the TDS protocol, it's just the way Profiler shows it as separate inserts. — Charlieface, Feb 19 '21 at 00:58

Steve Lovell · Answer 1 · 2021-02-18T15:11:17.747

0

If you're using a sufficiently recent version of SQL Server (2016 or later), STRING_SPLIT could be useful:

CREATE TABLE #MyTempTable (RandomNumber int)

INSERT INTO #MyTempTable
SELECT
    ss.Value
FROM
    STRING_SPLIT('1234,5678',',') ss

SELECT
    tt.RandomNumber
FROM
    #MyTempTable tt

edited Feb 18 '21 at 15:11

answered Feb 18 '21 at 14:35

Steve Lovell

2,564
2
13
16

score 0 · Answer 2 · answered Feb 18 '21 at 15:46

One way to handle this is to perform what I call an "Indexed Split".

For that you would need a tally (AKA numbers) table.

SET NOCOUNT ON;
USE tempdb;
GO

--==== 1. Numbers table setup
-- Create the table
IF OBJECT_ID('dbo.tally') IS NOT NULL DROP TABLE dbo.tally;
CREATE TABLE dbo.tally (N INT NOT NULL);

-- Add Primary Key (I do it here so that I can name it)
ALTER TABLE dbo.tally 
  ADD CONSTRAINT pk_cl_tally PRIMARY KEY CLUSTERED(N) 
    WITH FILLFACTOR=100;

-- Add a Unique Index (the optimizer will pick this one)
ALTER TABLE dbo.tally 
  ADD CONSTRAINT uq_tally UNIQUE NONCLUSTERED(N);

-- Add rows (100K should do)
INSERT dbo.tally
SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns, sys.all_columns a

Next for some sample data to be used for the split.

--==== 2. Sample data
CREATE TABLE dbo.Test (ID INT, Column1 VARCHAR(20));
INSERT INTO  dbo.Test (ID,Column1) 
  VALUES (1,'ORM;PS;SUP'),(2,'ABC;XYZ;999;123');

Now that we have our data, let's use our tally table to "pre-split" the string:

--==== 3. Indexed view to perform the "split"
-- The view
CREATE OR ALTER VIEW dbo.TestSplit WITH SCHEMABINDING AS 
SELECT 
  Id   = t.ID, 
  item = 
    SUBSTRING
    (
      t.Column1,
      tt.N+SIGN(tt.N-1),
      ISNULL(NULLIF((CHARINDEX(';',t.Column1,tt.N+1)),0),LEN(t.Column1)+1)-(tt.N)-SIGN(tt.N-1)
    ),
  ItemIndex = tt.N+1
FROM       dbo.Test  AS t
CROSS JOIN dbo.tally AS tt
WHERE      tt.N <= LEN(t.Column1)
AND        (tt.N = 1 OR SUBSTRING(t.column1,tt.N,1) = ';');
GO

-- The index
CREATE UNIQUE CLUSTERED INDEX uq_cl__testSplit ON dbo.TestSplit(Id,Item);
GO

Now you have the strings in the table, unchanged while the indexed view gives you a properly normalized view with your values "pre-split" and ready for retrieval.

--==== 4. Test and review execution plan
SELECT sv.* 
FROM   dbo.TestSplit AS sv;

Results:

Id          item                 ItemIndex
----------- -------------------- -----------
1           ORM                  2
1           PS                   5
1           SUP                  8
2           123                  13
2           999                  9
2           ABC                  2
2           XYZ                  5

Note the execution plan:

Lean and clean.

How can I store a long list in a SQL table?

2 Answers2