如何在从一个表更新到另一个表时分隔字段

本文介绍了如何在从一个表更新到另一个表时分隔字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

table1 --- column1 1002008000300-53-9 table2 --- column1 column2 column3 1002008000300 53 9 如何从table1填充到table2 SQL和ASP.Net（Csharp）

解决方案

数据集ds = GetYourDataFromTable1 尝试 { 创建连接 创建命令 命令的AddParameters（column1，column2，column3）

SqlTransaction trans; trans = connection.BeginTransaction String （）split; foreach （datarow row in ds.tables [ 0 ]。行）{ split = String .Split（row [ column1]。ToString， - ）; if （split.Length == 3 ） // 从split [0]，split [1]和split [2] 命令设置参数值。执行（）} // 每个 trans结束。提交 Catch Exception e { trans.Rollback }

我会通过带有三个参数的存储过程来完成...或者，如果数据库是Microsoft SQL Server，那么单个参数xml类型与第一个表中的column1并使用单个insert语句在数据库中执行所有操作。 上面的代码是1 for 1 如果这有帮助，请接受解决方案，以便其他人可以找到它。谢谢。

更新 t2 SET alldetails_cheque = t1.chequeno FROM table1 t1 INNER JOIN table2 ON t1.id = t2.id 和 t1.name = t2.name 其中 t1.adviceno null

假设SP带参数@id，@ chequeNo和@adviceNO

更新 t2 SET alldetails_cheque = CASE WHEN ISNULL（ @ adviceNO ，' '）= ' ' 那么 t1.chequeno ELSE alldetails_cheque END ， adviceno = CASE WHEN ISNULL（ @ chequeno ，' '）= ' ' 那么 t1.adviceNO ELSE adviceno END FROM table1 t1 INNER JOIN table2 ON t1.id = t2.id 和 t1.name = t2.name WHERE t2.id = @ id

您可以尝试使用SUBSTRING AND CHARINDEX分隔字符串，然后插入值。

WITH TAG AS （ SELECT ' PHP | JAVA | PYTHON' AS 类别） SELECT SUBSTRING（类别， 1 ，CHARINDEX（' |'，类别） - 1 ）类别 FROM TAG

输出 类别 PHP 这里已经解释了 SQL Server CHARINDEX函数 [ ^ ]

插入table2（column1，column2，column3） 选择 Parsename（替换（ column1，' - '，'。'），3）as column1 Parsename（替换（column1，' - '，'。'），2）as column2 PARSENAME（重place（column1，' - '，'。'），1）作为table1的column3 i得到了我的结果..

table1 --- column1 1002008000300-53-9 table2--- column1 column2 column3 1002008000300 53 9 how to populate from table1 to table2 SQL and ASP.Net(Csharp)

解决方案

Dataset ds = GetYourDataFromTable1 try { Create connection Create command AddParameters to the command (column1, column2, column3)

SqlTransaction trans; trans = connection.BeginTransaction String () split; foreach (datarow row in ds.tables[0].rows) { split = String.Split(row["column1"].ToString, "-"); if (split.Length == 3) //set parameter values from split[0], split[1] and split[2] command.Execute() } // end for each trans.Commit Catch Exception e { trans.Rollback }

I would do it via stored procedure with three parameters...or, if the database is Microsoft SQL Server then single parameter xml type with column1 from the first table and doing everything in the database with single insert statement. The above code is for going 1 for 1 If this helps, accept the solution so others may find it. Thank you.

UPDATE t2 SET alldetails_cheque = t1.chequeno FROM table1 t1 INNER JOIN table2 ON t1.id=t2.id and t1.name=t2.name where t1.adviceno is null

Single update for both conditions assuming SP with parameters @id, @chequeNo and @adviceNO

UPDATE t2 SET alldetails_cheque = CASE WHEN ISNULL(@adviceNO, '') = '' THEN t1.chequeno ELSE alldetails_cheque END, adviceno = CASE WHEN ISNULL(@chequeno, '') = '' THEN t1.adviceNO ELSE adviceno END FROM table1 t1 INNER JOIN table2 ON t1.id=t2.id and t1.name=t2.name WHERE t2.id = @id

You can try using SUBSTRING AND CHARINDEX to separate the string and then insert the values.

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category) SELECT SUBSTRING(Category, 1, CHARINDEX('|', Category) - 1) Category FROM TAG

Output Category PHP It has been explained here SQL Server CHARINDEX Function[^]

insert into table2(column1,column2,column3) Select Parsename(replace(column1,'-','.'),3) as column1 Parsename(replace(column1,'-','.'),2) as column2 Parsename(replace(column1,'-','.'),1) as column3 from table1 i have got my result..