how to insert to table string and foreign key by string split in sql-CodePudding

If we have a string as below with a foreign key

("c# , sql , php , html , css " , 22)

How can it be stored in a table that can be stored with any foreign keyword(by string split)?

id     word     foreign Key
1      c#        22
2      sql       22
3      php       22
4      html      22
5      css       22

CodePudding user response：

create table word(id int IDENTITY, wrd varchar(10), f_key int)

declare @foreignkey int = 22;
declare @string varchar(50) = 'c#,sql,php,html,css';

;with cte as
( select value from string_split(@string, ','))
insert into word select  value, @foreignkey from  cte;

select * from word;