Microsoft SQL Server，选择id并插入另一个表

I have this code for inserting some rows into the following table - and i am getting the id from another table that is already populated. Using Compact 4.0 Local Database Here with C# and Razor.

我有这个代码用于在下表中插入一些行 - 我从另一个已经填充的表中获取id。使用Compact 4.0本地数据库这里使用C#和Razor。

get id from sortedWord table:

从sortedWord表中获取id:

Id | SortedWord
0    act

insert data into words table:

将数据插入单词表:

Id | Word | SortedId
0    cat    0
1    tac    0

    for (var i = 0; i < words.Count(); i++){
        queryString = "SELECT Id FROM SortedWords WHERE SortedWord = @0";
        var sortedId = db.QuerySingle(queryString, sortWord(words[i]));
        queryString = "INSERT INTO Words (Word, SortedId) VALUES (@0, @1)";
        db.Query(queryString, words[i], sortedId.Id);
    }

Trouble is the select statement is inefficient, is it possible to do this without a select statement, something like select into: I saw some examples here but cant make sense of it. http://technet.microsoft.com/en-us/library/ms189872(v=sql.105).aspx

麻烦的是select语句是低效的,是否可以在没有select语句的情况下执行此操作,例如select into:我在这里看到了一些例子但是无法理解它。 http://technet.microsoft.com/en-us/library/ms189872(v=sql.105).aspx

2 个解决方案

#1

Yes you can simply use:

是的你可以简单地使用:

INSERT Words (Word, SortedID)
SELECT  @0, ID
FROM    SortedWords
WHERE   SortedWord = @1;

Then your c# would become:

然后你的c#将成为:

queryString = " INSERT Words (Word, SortedID) SELECT @0, ID FROM SortedWords WHERE SortedWord = @1;"
db.Query(queryString, words[i], sortedwords[i]);

Or you could just embed your first query into your second:

或者您可以将第一个查询嵌入到第二个查询中:

queryString = "INSERT INTO Words (Word, SortedId) VALUES (@0, (SELECT TOP 1 Id FROM SortedWords WHERE SortedWord = @1))";
db.Query(queryString, words[i], sortWord(words[i]));

However, if you are using SQL Server 2008 or later, I would go one further and do all the inserts at once using a Table valued parameters. The first step would be to create the type:

但是,如果您使用的是SQL Server 2008或更高版本,我会更进一步,使用Table值参数一次完成所有插入操作。第一步是创建类型:

CREATE TYPE dbo.TwoStringList AS TABLE (Value1 VARCHAR(MAX), Value2 VARCHAR(MAX));

I have used a generic name so the type is more reusable. You can then create a procedure that accepts this type as a parameter:

我使用了通用名称,因此类型更可重用。然后,您可以创建一个接受此类型作为参数的过程:

CREATE PROCEDURE dbo.InsertWords @StringList dbo.TwoStringList READONLY
AS
    INSERT Words (Word, SortedID)
    SELECT  sl.Value1, sw.ID
    FROM    SortedWords sw
            INNER JOIN @StringList sl
                ON sw.SortedWord = sl.Value2;

Then you can pass this to an SQL command with something like this:

然后你可以将它传递给SQL命令,如下所示:

var datatable = new DataTable();
datatable.Columns.Add(new DataColumn("Value1", typeof(string)));
datatable.Columns.Add(new DataColumn("Value2", typeof(string)));

for (var i = 0; i < words.Count(); i++)
{
    var dr = datatable.NewRow();
    dr[0] = words[i];
    dr[1] = sortedwords[i];
    datatable.Rows.Add(dr);
}

using (var connection = new SqlConnection("your Connection String"))
using (var command = new SqlCommand("dbo.InsertWords", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter{ 
                                            ParameterName = "@StringList", 
                                            SqlDbType = SqlDbType.Structured,
                                            TypeName = "dbo.TwoStringList",
                                            Value = datatable
                                            });
    connection.Open();
    command.ExecuteNonQuery();
}

Example on SQL Fiddle

关于SQL小提琴的例子

#2

I'm not very familiar with C# syntax, but you can do something like this:

我对C#语法不太熟悉,但你可以这样做:

INSERT INTO Words (Word, SortedId) 
SELECT @0,id 
FROM sortedWords 
WHERE sortedWord = @0;

#1