拆分逗号分隔的字符串并插入到表中 (int) [英] Split comma delimited string and insert to a table (int)

问题描述

我有一个包含 3 列(First_ID,Second_ID,Third_ID)的表，所有列都是 int 列.

i have a table with 3 columns (First_ID,Second_ID,Third_ID) all columns are int columns.

现在我有 3 个值，第一个和第三个值是 int 值(1 和 0)，第二个值是逗号分隔的字符串 ('188,189,190,191,192,193,194')

Now I have 3 values, first and third values are int values (1 and 0), the second value is a comma delimited string ('188,189,190,191,192,193,194')

我应该采用什么方法来填充如下表格:

what should be my approach to populate the table like the bellow:

1   188 0
1   189 0
1   190 0
1   191 0
1   192 0
1   193 0
1   194 0

我尝试了不同的方法，但无法按我的意愿工作.

I have tried different ways but could not get it to work as i want.

提前致谢

推荐答案

使用 Split() 你在评论中提到的函数，

Using the Split() function you have mentioned in comments,

-- Variable holding comma separated values
DECLARE @Var VARCHAR(4000);
SET @Var =  '188,189,190,191,192,193,194'

-- Test Target Table
DECLARE @Target_Table TABLE  (First_ID INT,Second_ID INT,Third_ID INT) 

-- Insert statement
INSERT INTO @Target_Table
SELECT 1, CAST(Items AS INT) , 0 
FROM  dbo.Split(@Var, ',')  

-- Test Select
SELECT * FROM  @Target_Table  

结果集

╔══════════╦═══════════╦══════════╗
║ First_ID ║ Second_ID ║ Third_ID ║
╠══════════╬═══════════╬══════════╣
║        1 ║       188 ║        0 ║
║        1 ║       189 ║        0 ║
║        1 ║       190 ║        0 ║
║        1 ║       191 ║        0 ║
║        1 ║       192 ║        0 ║
║        1 ║       193 ║        0 ║
║        1 ║       194 ║        0 ║
╚══════════╩═══════════╩══════════╝  

这篇关于拆分逗号分隔的字符串并插入到表中 (int)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！