On Aug 18, 2011, at 12:24 PM, Massi wrote: > Hi everyone, I'm trying to implement an InsertFromSelect workaround to > handle the identity insert issue of SQL server. This is more or less > what I'm doing: > > class InsertFromSelect(Executable, ClauseElement) : > def __init__(self, table, select) : > self.table = table > self.select = select > > @compiler.compiles(InsertFromSelect, "mssql") > def visit_insert_from_select(element, compiler, **kw) : > tab_name = compiler.process(element.table, asfrom=True) > q = "SET IDENTITY_INSERT %s ON;" %(tab_name) > q += "INSERT INTO %s (%s) %s;" % (tab_name, > ', '.join([compiler.process(c) for c in element.table.c]), > compiler.process(element.select)) > q += "SET IDENTITY_INSERT %s OFF;" %(tab_name) > return q
it would be better to use the built in IDENTITY_INSERT facilities of the MSSQL dialect itself. Set the "isinsert" flag on your compiled object, and also set a new flag I can add called "_mssql_requires_identity_insert": @compiler.compiles(InsertFromSelect, "mssql") def visit_insert_from_select(element, compiler, **kw) : compiler.isinsert = True compiler._mssql_requires_identity_insert = True patch for testing: diff -r 44e239751af8 lib/sqlalchemy/dialects/mssql/base.py --- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011 -0400 +++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 12:32:22 2011 -0400 @@ -655,7 +655,9 @@ seq_column = tbl._autoincrement_column insert_has_sequence = seq_column is not None - if insert_has_sequence: + if getattr(self.compiled._mssql_requires_identity_insert, False): + self._enable_identity_insert = True + elif insert_has_sequence: self._enable_identity_insert = \ seq_column.key in self.compiled_parameters[0] else: if this works for you I can commit it with a short test and it will be in 0.7.3. > > insert_from_select = InsertFromSelect(new_table, old_table)) > engine.execute(insert_from_select) easier issue here, your construct also needs "autocommit" enabled if you are going to use connectionless execution: class InsertFromSelect(...): _execution_options = \ Executable._execution_options.union({'autocommit': True}) or better yet just subclass UpdateBase instead of (Executable, ClauseElement). will add a doc -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.