tsql - Insert multiple values to SQL table (loop like)

Question

I'm using TSQL.

I have two tables - asset_to_role and asset_to_payoff, both with 2 columns. I need to insert values for an asset to both tables. For asset_to_role table I need to insert 5 values (312, 315, 316, 318, 319). For each of those values I need to insert 5 values to asset_to_payoff table (2241, 2242,..., 2245). To illustrate what I mean here is an example of such insertion:

insert into asset_to_role values (asset_id, 312)
insert into asset_to_payoff values (asset_id, 2241)
insert into asset_to_payoff values (asset_id, 2242)
insert into asset_to_payoff values (asset_id, 2243)
insert into asset_to_payoff values (asset_id, 2244)
insert into asset_to_payoff values (asset_id, 2245)

Is there a method in SQL to perform the process in a repetitive manner?

question from:https://stackoverflow.com/questions/65919744/insert-multiple-values-to-sql-table-loop-like

Answer 1

We do have DML trigger could help you on that, you could use After Insert trigger so when you insert data into main table, the trigger will fire and do the rest of tasks.

CREATE TABLE asset_to_role (asset_id INT, VALUE INT)
CREATE TABLE asset_to_payoff (asset_id INT, VALUE INT)

GO
CREATE TRIGGER InsertTrigger ON asset_to_role
AFTER INSERT AS 
BEGIN 
insert into asset_to_payoff SELECT asset_ID,2241 FROM inserted
insert into asset_to_payoff SELECT asset_ID,2242 FROM inserted
insert into asset_to_payoff SELECT asset_ID,2243 FROM inserted
insert into asset_to_payoff SELECT asset_ID,2244 FROM inserted
insert into asset_to_payoff SELECT asset_ID,2245 FROM inserted
END

INSERT INTO dbo.asset_to_role VALUES (1,312)

SELECT * FROM dbo.asset_to_role
SELECT * FROM dbo.asset_to_payoff

the result would be like this