Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
ServiceStack.OrmLite extension for INSERT INTO ... SELECT
public static int InsertIntoSelect<TEntity>(this IDbConnection db, ISqlExpression expression) where TEntity : IHasId<int>
var modelDef = typeof(TEntity).GetModelMetadata();
var untypedExpression = expression.GetUntypedSqlExpression();
// Tokenize the select list
var commands = untypedExpression
.Substring("SELECT ".Length)
// Get field names from the tokenized list
var fields = commands
.ToDictionary(k => Regex.Match(k.Name, @"(?>.+ AS )?(?>""?\w+""?.)?""?(\w+)""?").Groups[1].Value, v => v);
// ToInsertStatement reorders all fields based on Model Metadata order.
string fullInsertStatement = db.ToInsertStatement(Activator.CreateInstance<TEntity>(), fields.Keys);
var reorderedSelectList = new List<string>();
foreach (var fieldDef in modelDef.FieldDefinitionsArray)
if (fieldDef.ShouldSkipInsert())
if (fields.ContainsKey(fieldDef.Name))
if (fields.ContainsKey(fieldDef.FieldName))
// rebuild the select expression using the re-ordered select list
untypedExpression.SelectExpression = reorderedSelectList
.Aggregate(new StringBuilder(),
(sb, f) => sb.AppendFormat("{0},", f),
sb =>
sb.Length--; // skip trailing comma
return $"SELECT {sb.ToString()}";
var sql = expression.ToSelectStatement();
string selectStatement = db.GetDialectProvider().MergeParamsIntoSql(sql, expression.Params);
// replace VALUES with the user query
string insertIntoSelectStatement = Regex.Replace(fullInsertStatement, "VALUES .*$", selectStatement);
int rowsAffected = db.ExecuteSql(insertIntoSelectStatement);
return rowsAffected;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.