Insert into... select

Hi mythz, is it possible to create a INSERT INTO SELECT in OrmLite?

I have a SqlExpression and I just want to insert all those record in a table, something like this:

var q = DB.From<SourceTable>()
        .Select(t => new 
        {
            Param = 1,
            Descr = t.Descr
        });

long result = DB.InsertIntoSelect<DestinationTable>(q);

And I expect the following output:

INSERT INTO DestinationTable (Param, Descr)
SELECT 1 as Param, t.Descr
FROM SourceTable

I saw there is a overload of Insert that allows a commandFilter but I don’t think it does what I’m asking. Thanks.

EDIT: if it’s not possible, how can I generate the full SQL statement, filters included, from a SqlExpression? So I can build a literal command using the SELECT statement generated by OrmLite. I saw that INSERT INTO SELECT syntax is pretty much the same across many dbms (mssql, postgresql, sqlite). I’m stuck trying to get the field names out from the Select Statement.

Also i’m trying to use ToInsertStatement to generate the statement automatically, but insertField is ignored:

I went around that using basically the code you use in the library…

You can add a feature request on UserVoice but it’s only going to be able to support a small subset of SqlExpression where the Select List needs to match exactly with the column names of the table you want to Insert.

You can generate a Select statement from an SqlExpression with:

var sql = q.SelectInto<T>();

As most arguments are parameterized the SQL needs to be executed including the parameters in q.Params.

Due to the flexibility of SqlExpression it’s not available as a list of field names, you can get the select expression from q.SelectExpression.

Some functionality that may be helpful although not related to OrmLite (but it’s used to parse SELECT expressions in AutoQuery) is that you can parse a SELECT expression using the ParseCommands() extension method:

var fields = q.SelectExpression.Substring("SELECT ".Length).ParseCommands();

It tokenizes the expression making it easy to access field names, aliases and function arguments if used. Some tests with examples is available at:

insertField is now being passed in this commit (also on MyGet).

So there is no way to get the list of field of the anonymous object? I see in fact you “discard” the expression after parse. Right now, I managed to let it work with something like this:

public static int InsertIntoSelect<TEntity>(this IDbConnection db, ISqlExpression expression) where TEntity : IHasId<int>
{
    // https://github.com/ServiceStack/ServiceStack.OrmLite/blob/67d57acbc65ab200219b12f5ed7a0ecb8ac19b69/src/ServiceStack.OrmLite/Expressions/SqlExpression.cs#L1279
    var sql = expression.ToSelectStatement();
    string selectStatement = db.GetDialectProvider().MergeParamsIntoSql(sql, expression.Params);
    string fullInsertStatement = db.ToInsertStatement(Activator.CreateInstance<TEntity>(),
        new[]
        {
            nameof(LRARisultato.AnalisiId),
            nameof(LRARisultato.TipoValore),
            nameof(LRARisultato.OperatoreRelazionale),
            nameof(LRARisultato.TestoCodificatoId),
            nameof(LRARisultato.RisultatoId),
            nameof(LRARisultato.Stato),
            nameof(LRARisultato.InviareALIS),
            nameof(LRARisultato.RisultatoPrincipale),
            nameof(LRARisultato.TipoInserimento),
            nameof(LRARisultato.DataOraRicezione),
            nameof(LRARisultato.DataModifica),
            nameof(LRARisultato.VersioneRecord),
        });
    string insertIntoSelectStatement = Regex.Replace(fullInsertStatement, "VALUES .*$", selectStatement);

    int risultatiInseriti = db.ExecuteSql(insertIntoSelectStatement);

    return risultatiInseriti;
}

The fixed array where you see all the nameofs should contain the same list of fields of the SELECT query.

EDIT: anyway, it would be nice if you could take my solution, and with the support of your internal APIs, write a more solid implementation.

You can extract a list of field names from an anonymous expression using the ExpressionUtils.GetFieldNames() helper, e.g:

var fieldNames = ExpressionUtils.GetFieldNames((Poco x) => new { x.Id, x.Name });

It’s a versatile method that’s able to extract a list of field names from a variety of different inputs as seen in theses tests.

And ok, that’s fine, but I was asking about extracting the “select list fields” from a SqlExpression. Because otherwise I have to specify the expression twice, once for the Select, and once for ToInsertStatement.

Pass the select expression once in your InsertIntoSelect method, then pass it through to your SqlExpression.

Yeah that should do, a bit strange to use tho. Right now, InsertIntoSelect accept the SqlExpression directly.

Anyway, I’m there but I can see that ToInsertStatement doesn’t keep the order of the fields I pass to that. They get messed around and my INSERT fails because the INSERT list and SELECT list are not synced anymore. I guess that ToInsertStatement produces fields in the same order that they appear in the TEntity item definition, am i right?

Kind of, that’s the current behavior in .NET but there’s explicitly no guarantee in the order returned in .NET Reflection APIs (so could change in future), when needing exact ordering fetch it from the ModelDefinition, e.g:

var modelDef = typeof(MyType).GetModelMetadata();
var orderedFieldNames = modelDef.FieldDefinitionsArray
    .Where(x => !x.ShouldSkipInsert())
    .Map(x => x.FieldName);

Ok so, since ToInsertStatement enforces a custom order, I should re-order the select statement fields based on that order?

That could be a problem… since I’m using aliases, I call ToInsertStatement with fieldNames equals to .net property names, but the output (correctly) contains Aliases, so it’s a bit complicated to reorder my select list based on the order produced by ToInsertStatement. Also because GetModelMetadata gives me back the model of the From<> but if I have multiple tables i’m screwed.

Ok, I’ve managed to let it work like this:

That’s an option, but IMO it’s better to leave the SELECT expression untouched and rearrange the Insert fields to adopt the order of insertFields if any were provided.

In order to be able to do this I updated OrmLite to adopt the ordering of insertFields in this commit.

I’ve also added a built-in InsertIntoSelect<T> API adopting this approach in this commit which leads to a simpler impl.

The new built-in InsertIntoSelect<T> API is available from v5.4.1 that’s now available on MyGet, if you already have v5.4.1
installed you’ll need to clear your NuGet cache.

Wow thank you! I tried your new implementation, but I run in an error:

Resulting INSERT:

INSERT INTO "LRARISULTATI" ("AANALISIID",
                            "TIPOVALORE",
                            "OPERATORERELAZIONALE",
                            "DTESTOCODIFICATOID",
                            "DRISULTATOID",
                            "STATO",
                            "TIPOINSERIMENTO",
                            "DATAORARICEZIONE",
                            "DATAMODIFICA",
                            "VERSIONERECORD")
SELECT DISTINCT "LRAANALISI"."IDAANALISI" AS "AnalisiId",
                1 AS TipoValore,
                N'' AS OperatoreRelazionale,
                1 AS TestoCodificatoId,
                "LRDRISULTATI"."IDDRISULTATO" AS "RisultatoId",
                0 AS Stato,
                "LRDRISULTATI"."INVIAREALIS", --missing?
                "LRDRISULTATI"."RISULTATOPRINCIPALE", --missing?
                0 AS TipoInserimento,
                N'20181129 09:49:54.5600742' AS DataOraRicezione,
                N'20181129 09:49:54.5600742' AS DataModifica,
                1 AS VersioneRecord
FROM "LRACONTENITORI"
INNER JOIN "LRARICHIESTE" ON ("LRACONTENITORI"."ARICHIESTAID" = "LRARICHIESTE"."IDARICHIESTA")
INNER JOIN "LRAANALISI" ON ("LRACONTENITORI"."IDACONTENITORE" = "LRAANALISI"."ACONTENITOREID")
INNER JOIN "LRDANALISI" ON ("LRAANALISI"."DANALISIID" = "LRDANALISI"."IDDANALISI")
INNER JOIN "LRDRISULTATI" ON ("LRDANALISI"."IDDANALISI" = "LRDRISULTATI"."DANALISIID")
LEFT JOIN "LRARISULTATI" ON (("LRAANALISI"."IDAANALISI" = "LRARISULTATI"."AANALISIID")
                             AND ("LRDRISULTATI"."IDDRISULTATO" = "LRARISULTATI"."DRISULTATOID"))
WHERE ("LRARICHIESTE"."ARCHIVIATA" = @0)
  AND ("LRACONTENITORI"."DATAORAPRIMOCHECKIN" IS NOT NULL)
  AND ("LRACONTENITORI"."DATAORAESECUZIONE" IS NOT NULL)
  AND ("LRARISULTATI"."IDARISULTATO" IS NULL)
  AND ("LRARICHIESTE"."DATAORAACCETTAZIONE" < @1)

Two field from the select list are missing from the Insert Fields.

Also, I was looking at your tests. I think a function like this should return the number of rows affected instead of the ID, since 99% of the time an INSERT like this will produce more than 1 record.
Thanks!

Please provide a stand-alone example I can run to repro the issue.

It was already returning the number of rows inserted, the test just used wrong variable name that’s now resolved.

Right now the ordering of insertFields works inconsistently across DialectProviders. Right now it works fine with SqliteDialect but not with SqlServer2016Dialect. It works with SqlServerDialect tho.

I’ve tested the InsertIntoSelect test which works with all RDBMS providers including SqlServer2016, please provide a stand-alone example that doesn’t.

Use yours. Change the order of the fields, trying to not respect the order of the properties in the SubUserAuth poco. The only reason your test is working is because your anonymous object has the fields in the same order they are defined into SubUserAuth.

ok that was primarily an issue with parsing commands that’s now resolved in latest v5.4.1 on MyGet.

FYI if you’re using .ParseCommands() in your own code you’ll likely want to switch to use command.Original as that now contains the original string for each selected field whereas Name would only contain the name of the Function if it starts with one.

Here’s the repo with the issue I currently have. It’s seems to be because of the DateTime2 converter.

Let me know, thanks a lot!

This runs without issue using the latest OrmLite:

The only thing I’ve changed is the connectionString to use my local SQL Server instance.

Ok my OrmLite.SqlServer was still at 5.4.0. Only OrmLite base package was at 5.4.1.

Thanks for the help!