如何在表中插入字符串值

Question

使用 VB6 和 SQL Server

如何在表中插入字符串值

Dim a as string
a = 0008", "0009", "1011", "1208

我想在表中插入该字符串值

预期输出

Table1

ID

0008
0009
1011
1208

如何针对上述条件进行查询。

需要查询帮助。

Answer 1

请尝试以下方法：

Dim a as string
a = "0008,0009,1011,1208"
dim strArray as string() = Split(a, ",")

b
对于strArray中的每个项目作为字符串

   query = "INSERT INTO  Table1(ID) values('"&item&"')"
   // Write Connection and Command code here
next

更新：

您可以一次为每个项目生成插入SQL语句，然后编写连接和命令代码，这样会更有效。

dim query as string
for each item as string in strArray
   query = query + "INSERT INTO  Table1(ID) values('"&item&"'); "
next
// Write Connection and Command code here after generating insert statement 
// so that all data will save into database in one shot.

Answer 2

DECLARE @Str VARCHAR(100)
SET @Str = '0008", "0009", "1011", "1208'
SET @Str = REPLACE(@Str,'"','')
SELECT * FROM [dbo].Split(@Str) 

您可以从此链接使用拆分功能。
检查这个分割功能。
http:// /blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/split-string-in-sql-server-2005-clr-vs-t

Answer 3

首先，您必须将此字符串拆分为字符串[]

dim a as string 

a = 0008", "0009", "1011", "1208
dim str as string() = (a.Replace('"',' ')).Split(",")

，如果

a = "0008,0009,1011,1208"
dim str as string() = a.Split(",")

现在使用 foreach 循环上的查询在表中添加值，则

您的表名称为 testTable，列名称为 ID

 dim query as string
 for each item as string in str

            query = "INSERT INTO  testTable(ID) values('"&item&"')"
 next