SQL查询的大参数列表

I have a big list of int parameters for a SQL query:

我有一个SQL查询的大型int参数列表：

update mytable set col='xyz'
where id in (/* thousands of ints */)

My problem is that in SQL Server 2000 there are a limit for parameters. I could run this query on an SQL Server 2008 too.

我的问题是在SQL Server 2000中有参数限制。我也可以在SQL Server 2008上运行此查询。

What is the better way to do this.

有什么更好的方法来做到这一点。

Edit:

编辑：

The list of Ids come from a C# program. Not from another table.

Ids列表来自C＃程序。不是从另一张桌子。

7 个解决方案

#1

You can insert the integers into a temp table, and then query like this:

您可以将整数插入临时表，然后像这样查询：

update mytable m set col='xyz' 
where exists (select * from #MyTempTable where id = m.id)

#2

An alternate approach that works with SQL 2000 is to use XML.

与SQL 2000一起使用的另一种方法是使用XML。

Have the program/application format the ints like so:

程序/应用程序格式如下所示：

'<root><TMP J="111"/><TMP J="222"/><TMP J="333"/></root>'

.
Then create the following stored procedure:

。然后创建以下存储过程：

CREATE PROCEDURE UpDateIntsFromXML (
    @sXML TEXT
)
AS
    DECLARE @iDoc INT
    EXEC    sp_xml_preparedocument @iDoc OUTPUT, @sXML

    UPDATE  YourTable
    SET     YourColumn = 'fixed value'
    FROM    OPENXML (@iDoc, '/root/TMP', 1) WITH (J INT) AS X
    WHERE   X.J = YourTable.IntColumn

    EXEC    sp_xml_removedocument @iDoc
RETURN

.
Then your application can call that SP, passing a potentially huge block of text/XML.

。然后你的应用程序可以调用该SP，传递一个潜在的大块文本/ XML。

Observe that root, TMP, and J are all case-sensitive.

注意root，TMP和J都是区分大小写的。

#3

The best working soulution for me was SQL Server 2008: Table Valued Parameters

对我来说最好的工作原理是SQL Server 2008：表值参数

100000 Ids needs 14-20s, 1000 Ids needs ~140ms.

100000 Ids需要14-20s，1000 Ids需要~140ms。

sql = @"
  update MyTable
    set Col1 = 1
    where ID in (select * from @ids)
  ";
sqlCmd = new SqlCommand {Connection = _sqlConn, CommandText = sql};

//Create a DataTable with one Column("id") and all ids as DataRows
DataTable listOfLeadIDs = new DataTable();
listOfIDs.Columns.Add("id", typeof(int));
Ids.ToList<string>().ForEach(x => listOfIDs.Rows.Add(new object[] { int.Parse(x) }));

//Bind this DataTable to the Command-object
// Node: "IntTable" is an User-Defined-Table-Typ (new feature with SQL-2008)
sqlCmd.Parameters.Add(
  new System.Data.SqlClient.SqlParameter("@ids", listOfIDs) { 
    TypeName = "IntTable" 
  });

//Execute the Query
sqlCmd.ExecuteNonQuery();

The User-Defined-Table-Typ:

用户定义的表格类型：

CREATE TYPE [dbo].[IntTable] AS TABLE(
    [id] [int] NULL
)
GO

#4

At all costs, AVOID IN; especially if you are post-2000. my backup

不惜一切代价，避免进入;特别是如果你是2000年以后。我的备份

Instead, use EXISTS

相反，使用EXISTS

UPDATE myTable
SET col = 'newValue'
FROM myTable 
WHERE EXISTS (
      SELECT * 
      FROM @myTempTable temp
      WHERE myTable.ID = temp.ID)

#5

Divide the data into smaller groups, and execute multiple update queries.

将数据划分为更小的组，并执行多个更新查询。

There is no reason to use a temp table, since you retrieve the data from outside the db, so there is no way to avoid its transfer towards the db.

没有理由使用临时表，因为您从数据库外部检索数据，因此无法避免将数据传输到数据库。

#6

If the ints are in any way sequential (more than two at once), you could make them into BETWEEN pairs.

如果int以任何方式顺序排列（一次超过两个），则可以将它们变为BETWEEN对。

But in this case, just make a string of these ints and pass that as a single varchar(max) parameter.

但在这种情况下，只需创建这些int的字符串并将其作为单个varchar（max）参数传递。

#7

-2

I think you probably want to create a memory based temporary table with an index. Assuming the table you are querying against is large you would not want to do a table scan comparing each row against each of your 5000 matches. You want to do a join with the help of two indexes.

我想你可能想要创建一个带索引的基于内存的临时表。假设您查询的表很大，您不希望进行表扫描，将每行与5000个匹配项进行比较。您希望在两个索引的帮助下进行连接。

CREATE TEMPORARY TABLE IF NOT EXISTS inputlist
(i INT PRIMARY KEY) ENGINE = MEMORY;

INSERT INTO inputlist (i) VALUES (1),(2),(3),(1000),(2000),(5000);

SELECT * FROM your_table JOIN inputlist ON your_table.intvalues = inputlist.i;

DROP TEMPORARY TABLE inputlist;

SQL based on MySQL, see:
http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/insert.html
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

基于MySQL的SQL，请参阅：http：//dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/ insert.html http://dev.mysql.com/doc/refman/5.1/en/create-table.html

#1