Как я могу сохранить длинный список в таблице SQL?

#sql #sql-server #tsql #split

#sql #sql-server #tsql #разделение

Вопрос:

У меня очень длинная строка случайных чисел, например, 234 364 …, 632. Я хочу вставить эту строку во временную таблицу SQL, чтобы я мог использовать ее в IN (SELECT * FROM #MYTABLE) инструкции различных запросов. Как я могу это сделать? По-видимому, нет простого способа вставить этот список в столбец в таблице. Я думал вставить его в виде строки, а затем повернуть таблицу. Любое решение, пожалуйста?

Комментарии:

1. Пожалуйста, не делайте этого; вместо этого сохраните каждое число в отдельной записи.

2. В tsql нет таких понятий, как «список» или «массив». У вас есть строка (скалярное значение — возможно, локальная переменная или параметр), содержащая числа, разделенные запятыми?

3. Вы сидите и смотрите на реляционную систему хранения данных и пытаетесь понять, как не использовать ее в качестве реляционного хранилища. Почему? Делайте то, что говорит @TimBiegeleisen.

4. Хорошо, но каково ваше предложение на самом деле? Числа в строке меняются, и я хочу легко обновить список. Затем я хочу использовать обновленный список в различных операторах в рамках одного и того же запроса. Каков наилучший подход? Очевидно, что я не могу вставить каждое число в таблицу, это создаст запрос в 4000 строк, поскольку мой список содержит 4000 чисел.

5. Используйте табличный параметр

Ответ №1:

Если вы используете достаточно последнюю версию SQL Server (2016 или более позднюю), STRING_SPLIT может быть полезно:

 CREATE TABLE #MyTempTable (RandomNumber int)

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

SELECT
    tt.RandomNumber
FROM
    #MyTempTable tt
 

Ответ №2:

Один из способов справиться с этим — выполнить то, что я называю «индексированным разделением«.

Для этого вам понадобится таблица подсчета (она же numbers).

 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
 

Далее приведены некоторые примеры данных, которые будут использоваться для разделения.

 --==== 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');
 

Теперь, когда у нас есть наши данные, давайте используем нашу таблицу подсчета для «предварительного разделения» строки:

 --==== 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
 

Теперь у вас есть строки в таблице без изменений, в то время как индексированное представление дает вам правильно нормализованное представление с вашими значениями, «предварительно разделенными» и готовыми к извлечению.

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

Результаты:

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

Обратите внимание на план выполнения:

введите описание изображения здесь

Бережливый и чистый.