sqlserver 数组

DECLARE @Array TABLE (ID INT, Name NVARCHAR(50));

INSERT INTO @Array (ID, Name) VALUES (1, 'Alice');
INSERT INTO @Array (ID, Name) VALUES (2, 'Bob');

SELECT * FROM @Array;

DECLARE @XmlData XML = '<People><Person><ID>1</ID><Name>Alice</Name></Person><Person><ID>2</ID><Name>Bob</Name></Person></People>';

SELECT 
    Person.ID.value('ID[1]', 'INT') AS ID,
    Person.ID.value('Name[1]', 'NVARCHAR(50)') AS Name
FROM @XmlData.nodes('/People/Person') AS Person(ID);

DECLARE @JsonData NVARCHAR(MAX) = N'[{"ID": 1, "Name": "Alice"}, {"ID": 2, "Name": "Bob"}]';

SELECT 
    JSON_VALUE(Item, '$.ID') AS ID,
    JSON_VALUE(Item, '$.Name') AS Name
FROM OPENJSON(@JsonData) AS Item;

CREATE TABLE #Array (ID INT, Name NVARCHAR(50));

INSERT INTO #Array (ID, Name) VALUES (1, 'Alice');
INSERT INTO #Array (ID, Name) VALUES (2, 'Bob');

SELECT * FROM #Array;

DROP TABLE #Array;

-- 使用临时表代替表变量
CREATE TABLE #LargeArray (ID INT, Name NVARCHAR(50));

INSERT INTO #LargeArray (ID, Name)
SELECT ID, Name FROM YourLargeTable;

-- 分批处理数据
DECLARE @BatchSize INT = 1000;
DECLARE @Offset INT = 0;

WHILE (1=1)
BEGIN
    SELECT * 
    FROM #LargeArray 
    ORDER BY ID 
    OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY;

    IF @@ROWCOUNT < @BatchSize BREAK;
    SET @Offset = @Offset + @BatchSize;
END;

DROP TABLE #LargeArray;