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.
