From f793a88403c01600c45958290bec54bebced5a28 Mon Sep 17 00:00:00 2001
From: Michael Trier <mtrier@gmail.com>
Date: Fri, 2 Jan 2009 18:24:57 +0000
Subject: [PATCH] Added ability to use subselects within INSERTS on mssql.

---
 CHANGES                           |  4 ++++
 lib/sqlalchemy/databases/mssql.py | 27 +++++++++++++++++++++++++++
 test/orm/unitofwork.py            |  1 -
 3 files changed, 31 insertions(+), 1 deletion(-)

diff --git a/CHANGES b/CHANGES
index f2e007d0ae..1167519026 100644
--- a/CHANGES
+++ b/CHANGES
@@ -272,6 +272,10 @@ CHANGES
       new doc section "Custom Comparators".
     
 - mssql
+    - If an ``INSERT`` includes a subselect the ``INSERT`` is
+      converted from an ``INSERT INTO VALUES`` construct to a
+      ``INSERT INTO SELECT`` construct.
+
     - If the column is part of a ``primary_key`` it will be
       ``NOT NULL`` since MSSQL doesn't allow ``NULL`` in primary_key
       columns.
diff --git a/lib/sqlalchemy/databases/mssql.py b/lib/sqlalchemy/databases/mssql.py
index 265150413f..5ad2a93069 100644
--- a/lib/sqlalchemy/databases/mssql.py
+++ b/lib/sqlalchemy/databases/mssql.py
@@ -1494,6 +1494,33 @@ class MSSQLCompiler(compiler.DefaultCompiler):
                 return self.process(expression._BinaryExpression(binary.left, binary.right, op), **kwargs)
             return super(MSSQLCompiler, self).visit_binary(binary, **kwargs)
 
+    def visit_insert(self, insert_stmt):
+        insert_select = False
+        if insert_stmt.parameters:
+            insert_select = [p for p in insert_stmt.parameters.values() if isinstance(p, sql.Select)]
+        if insert_select:
+            self.isinsert = True
+            colparams = self._get_colparams(insert_stmt)
+            preparer = self.preparer
+
+            insert = ' '.join(["INSERT"] +
+                              [self.process(x) for x in insert_stmt._prefixes])
+
+            if not colparams and not self.dialect.supports_default_values and not self.dialect.supports_empty_insert:
+                raise exc.NotSupportedError(
+                    "The version of %s you are using does not support empty inserts." % self.dialect.name)
+            elif not colparams and self.dialect.supports_default_values:
+                return (insert + " INTO %s DEFAULT VALUES" % (
+                    (preparer.format_table(insert_stmt.table),)))
+            else:
+                return (insert + " INTO %s (%s) SELECT %s" %
+                    (preparer.format_table(insert_stmt.table),
+                     ', '.join([preparer.format_column(c[0])
+                               for c in colparams]),
+                     ', '.join([c[1] for c in colparams])))
+        else:
+            return super(MSSQLCompiler, self).visit_insert(insert_stmt)
+
     def label_select_column(self, select, column, asfrom):
         if isinstance(column, expression._Function):
             return column.label(None)
diff --git a/test/orm/unitofwork.py b/test/orm/unitofwork.py
index 1083435d71..45ecc66e15 100644
--- a/test/orm/unitofwork.py
+++ b/test/orm/unitofwork.py
@@ -658,7 +658,6 @@ class ClauseAttributesTest(_base.MappedTest):
         eq_(u.name, 'test2')
         eq_(u.counter,  2)
 
-    @testing.crashes('mssql', 'FIXME: unknown, verify not fails_on()')
     @testing.resolve_artifact_names
     def test_insert(self):
         u = User(name='test', counter=sa.select([5]))
-- 
2.47.3