如何在从一个表更新到另一个表时分隔字段 [英] how to separate fields while updating from one table to another

问题描述

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 ，' '）= ' < span class =code-string>' 那么 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分隔字符串，然后插入值。< br $>

  WITH  TAG  AS （ SELECT  '  PHP | JAVA | PYTHON'  AS 类别）
  SELECT  SUBSTRING（类别， 1 ，CHARINDEX（'  |'，类别） -  < span class =code-digit> 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..

这篇关于如何在从一个表更新到另一个表时分隔字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！