从另一个表中插入多行，并在SQL Server 2000中获取插入行的ID

I have table A and table B, table B has the same columns of A and only one extra column x , I want to insert some rows from A to B , and then I want to update each one of these last inserted rows with a new value for column x.

我有表A和表B,表B有相同的A列,只有一个额外的列x,我想插入一些从A到B的行,然后我想用新的更新这些最后插入的行中的每一行第x列的值。

How can I do it in SQL Server 2000?

我怎么能在SQL Server 2000中做到这一点?

Now I am using this query in C# code, I select all wanted rows from A and I save it in DataTable

现在我在C#代码中使用此查询,我从A中选择所有想要的行,然后将其保存在DataTable中

string Query = "Select * from A where ID = 5";

SqlDataAdapter dap = new SqlDataAdapter(Query, Conn);
dap.Fill(DataTablle1);

foreach (DataRow row in DataTablle1.Rows)
{
      string query2 = "Insert into B (col a , col b, col x) values ('" + row["col a"] + "','" + row["col b"] + "',3)";
}

This way requires many insert statement depending on the number of rows in table A, is there any way to achieve it with minimum number of insert statement?

这种方式需要很多insert语句,具体取决于表A中的行数,有没有什么方法可以用最少的insert语句来实现它?

1 个解决方案

#1

You could just have an insert-select statement with the literal 3:

您可以只使用文字3的insert-select语句:

INSERT INTO b ([col a] , [col b], [col x])
SELECT [col a], [col b], 3
FROM   a 
WHERE  id = 5