从SELECT语句存储多个值,然后插入到不同的表中 [英] Store multiple values from SELECT statement and then insert into different table
问题描述
我想基于列表框中的多个值使用 SELECT
语句到表中并获取多个ID,然后运行 INSERT
语句并将多个ID存储到一个 INSERT
语句中的另一个表.
I want to use a SELECT
statement into a table based on multiple values from ListBox and get multiple IDs and then run an INSERT
statement and store the multiple IDs into a different table from INSERT
statement.
下面的代码无法正常工作,因为我在单行而不是在多个行中获得多个ID.
My code below is not working as I am getting "NULL" in single row instead of multiple IDs in multiple rows.
我正在对所有SQL语句使用存储过程.
I am using a stored procedure for all the SQL statements.
请在下面查看我的代码:
Please see my code below:
我的ASPX网页的代码背后:
Code-behind of my ASPX web page:
string listboxvalue = "";
foreach (ListItem item in listbox.Items)
{
if (item.Selected)
{
listboxvalue += item.Text + ',';
}
}
listboxvalue = listboxvalue.Substring(0, listboxvalue.Length - 1);
cmd.Parameters.AddWithValue("spselectvalue", listboxvalue);
存储过程:
@spselectvalue nvarchar(MAX),
// Select multiple Ids based on multiple items from list box
DECLARE @Dis TABLE (DisID int)
INSERT INTO @Dis
SELECT DId
FROM [table name]
WHERE [COLUMN] IN ('+@spselectvalue +')
EXEC sp_executesql @Dis
// Insert multiple Ids (from above select statement) into different table
INSERT INTO [dbo].[DifferentTable] ([SelectedIds])
VALUES
(
(SELECT DisID from @Dis)
)
推荐答案
似乎您的问题是您不知道如何将ID列表放入存储过程中进行处理.希望这个例子会有所帮助.如果采用这种方式,您将不必弄乱逗号分隔的字符串或动态SQL.
Seems like your problem is that you don't know how to get a list of ID's into a stored procedure for processing. Hopefully this example will help. If you do it this way, you won't have to mess with comma-delimited strings or dynamic SQL.
首先,定义一个可以包含ID列表的SQL类型:
First, define a SQL type that can contain your list of IDs:
CREATE TYPE dbo.MyList
AS TABLE
(
ID VarChar(50)
);
然后,编写一个接受此类型作为其输入参数的存储过程:
Then, write a stored procedure that accepts this type as its input parameter:
CREATE PROCEDURE dbo.InsertAList
@List AS dbo.MyList READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[DifferentTable]
SELECT ID FROM @List
END
现在将您的数据从c#端绑定到存储过程.要绑定它,您必须首先将数据存储在 DataTable
中,这相对容易:
Now to bind your data to the stored procedure from the c# end. To bind it, you have to store the data in a DataTable
first, which is relatively easy:
var table = new DataTable();
table.Columns.Add("ID", typeof(string));
foreach (ListItem item in listBox.Items.Where(i => i.Selected))
{
table.Rows.Add(item.Text);
}
然后将表提交给存储过程,如下所示:
Then submit the table to the stored procedure like so:
var cmd = new SqlCommand()
{
CommandType = CommandType.StoredProcedure,
CommandText = "InsertAList",
Connection = myConnection
};
cmd.Parameters.Add(new SqlParameter("@List", SqlDbType.Structured)
{
TypeName = "dbo.MyList",
Value = table
});
cmd.ExecuteNonQuery();
这篇关于从SELECT语句存储多个值,然后插入到不同的表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!