将字符串的每个值插入到单个列中 [英] Insert string's each values into a single column

问题描述

这里我想将每个值的完整字符串插入到一列中.为此，我编写了以下脚本:

Here I want to insert the complete string each values into a column. For which I have written the following script:

示例:

表格:测试

create table test
(
cola varchar(10),
colb varchar(max),
colc varchar(10)
);

注意:现在我想通过调用存储过程插入如下记录:

Note: Now I want to insert records like the following by calling stored procedure:

cola   colb   colc
------------------
X1     M1     Z1
X1     M2     Z1
X1     M3     Z1
X1     M4     Z1

存储过程:sptest

CREATE PROC sptest
@cola varchar(10),
@colb varchar(max),
@colc varchar(10)

AS

Declare @dynamic varchar(max)

    SET @dynamic =N'delete from test where colc='''+ @colc +'''';
    PRINT(@dynamic)
    EXEC(@dynamic)

    SET @dynamic =N'insert into test values('''+@cola+''','''+@colb+''','''+@colc+''')';
    PRINT(@dynamic)
    EXEC(@dynamic)

GO

注意:首先，我需要通过检查 colc 值来删除记录，然后插入记录.

Note: First I need to delete the records by check with the colc values and after that insert the records.

调用函数:

EXEC sptest
@cola = 'X1',
@colb = 'M1,M2,M3,M4',
@colc = 'Z1'

注意:在如上所示的调用函数中，colb 值必须插入，如上表所示.我不知道如何在 column colb 中插入每个值的完整字符串.

Note: In the calling function as shown above the colb values must insert as shown in the above table. I am not getting how to insert the complete string each values in the column colb.

推荐答案

SQL 没有任何自动将逗号分隔值字符串(您的 @colb 变量)拆分为多个插入的机制.您需要编写一些代码来自己进行拆分.

SQL does not have any mechanism for automatically splitting a string of comma-separated values (your @colb-variable into multiple inserts. You will need to write some code to do this splitting yourself.

基本上，你应该这样做:

Basically, you should do something like this:

1. 在while循环中，使用CHARINDEX来确定下一个,在@colb中的位置.将此位置存储在变量中.
2. 使用 SUBSTRING 仅检索从 @colb 到存储在 (1) 中的位置的字符.
3. 使用 @cola、@colc 和您在 (2) 中提取的值调用 INSERT.
4. 重复直到在@colc 中找不到 ,.

1. In a while loop, use CHARINDEX to determine the position of the next , in @colb. Store this position in a variable.
2. Use SUBSTRING to retrieve only the characters from @colb up to the position stored in (1).
3. Call INSERT with @cola, @colc and the value you extracted in (2).
4. Repeat until no more , found in @colc.

这篇关于将字符串的每个值插入到单个列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！