CODEWITHSUNDEEP
arrayssql-serverloops
Харламов Даниил
Харламов Даниил

Reputation: 163

How to insert records into table in loop?

I have a table with non-autoincrement ID field. I need to create an varchar array and then insert data from array into table.

The problem is that I don't know how to declare such array. And also I don't know how to address the value by the index in the loop.

declare @newCodesList - ???
declare @counter int = 0
declare @lastID int = (select MAX(Id) from OrganizationCode)

while @counter < LEN(@newCodesList)
begin
    @lastID = @lastID + 1

    insert into OrganizationCodeCopy values(@lastID, @newCodesList[@counter])

    @counter = @counter + 1
end

In code upper I try to insert values in the loop after finding last record ID and declaring counter

Upvotes: 0

Views: 1096

Answers (3)

sticky bit
sticky bit

Reputation: 37472

You could use a table variable to store the codes in. Then do an INSERT ... SELECT from that variable. To get the IDs you can use row_number() the your maximum ID from the other table.

DECLARE @codes TABLE
               (code nvarchar(4));

INSERT INTO @codes
            (code)
            VALUES ('A01B'),
                   ('B03C'),
                   ('X97K');

INSERT INTO organizationcodecopy
            (id,
             code)
            SELECT (SELECT coalesce(max(id), 0)
                           FROM organizationcode) + row_number() OVER (ORDER BY code) id,
                   code
                   FROM @codes;

db<>fiddle

Upvotes: 1

Zhorov
Zhorov

Reputation: 29963

Using loops is not a good solution. You may try to define a temporary table and insert new data with one statement. Values for Id are generated with ROW_NUMBER() function:

-- New data
CREATE TABLE #NewCodes (
    Code varchar(50)
)
INSERT INTO #NewCodes
    (Code)
VALUES
    ('Code1'),
    ('Code2'),
    ('Code3'),
    ('Code4'),
    ('Code5'),
    ('Code6')

-- Last ID
DECLARE @LastID int
SELECT @LastId = (SELECT ISNULL(MAX(Id), 0) FROM OrganizationCode)

-- Statement
INSERT INTO OrganizationCode
    (Id, Code)
SELECT
    @LastId + ROW_NUMBER() OVER (ORDER BY Code) AS Id,
    [Code]
FROM #NewCodes

Upvotes: 1

Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

Instead of an array, you can use a comma separated string. Like following:

DECLARE @newCodesList VARCHAR(MAX) = 'value1,value2'
DECLARE @lastID int = (SELECT MAX(Id) FROM OrganizationCode)

INSERT INTO OrganizationCodeCopy 
(
    Id, 
    Code
)
SELECT 
    @lastID + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id
    Element AS Code
FROM 
    asi_SplitString(@newCodesList, ',')

Upvotes: 1

Related Questions