Issue 1:
Migrated issue, originally created by Michael Schurter (@schmichael)

Support batch inserts using multiple values() calls like:

>>> tbl.insert().values(a=1, b=1).values(a=2, b=2)
INSERT INTO tbl (a, b) VALUES (1, 1) (2, 2);

This would make tbl.insert().values(...).values(...) conceptually similar to tbl.select().where(...).where(...) where multiple calls extend the existing query instead of replacing it.

Issue 2:
Describe the bug

Reflecting a table's columns using a combination of the Microsoft ODBC SQL Server drivers with SQL Server 2005 raises an exception.

Expected behavior

I would expect to get a list of columns in the table with no exception (which happens with the FreeTDS driver).

To Reproduce

from sqlalchemy import create_engine, inspect

source_engine = create_engine("mssql+pyodbc:///?odbc_connect=[OMITTED]")

insp = inspect(source_engine)
insp.get_columns('TABLE NAME', schema='SCHEMA NAME')

Error

Traceback (most recent call last):
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\default.py", line 593, in do_execute
cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'sp_columns'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\reflection.py", line 390, in get_columns
col_defs = self.dialect.get_columns(
File "<string>", line 2, in get_columns
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\reflection.py", line 52, in cache
ret = fn(self, con, *args, **kw)
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2195, in wrap
return _switch_db(
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2219, in _switch_db
return fn(*arg, **kw)
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 2741, in get_columns
cursor = connection.execute(
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 2235, in execute
return connection.execute(statement, *multiparams, **params)
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1011, in execute
return meth(self, multiparams, params)
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1124, in _execute_clauseelement
ret = self._execute_context(
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1510, in _handle_dbapi_exception
util.raise_(
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\util\compat.py", line 182, in raise_
raise exception
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "C:\somewhere\miniconda3\envs\migration\lib\site-packages\sqlalchemy\engine\default.py", line 593, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'sp_columns'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")
[SQL: sp_columns @table_name = ?, @table_owner = ?]
[parameters: ('XXX', 'YYY')]
(Background on this error at: http://sqlalche.me/e/13/f405)

Versions.

OS: Windows 10 [& Debian 10 in docker image on a RedHat 7 host]

Python: 3.9

SQLAlchemy: 1.3.21

Database: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

DBAPI: pyodbc 4.0.30

Drivers:

SQL Server 10.00.14393.00

SQL Server Native Client 11.0 2011.110.7462.06

ODBC Driver 17 for SQL Server 2017.174.01.01

Additional context

This bug doesn't happen with the FreeTDS Linux driver, or with never versions of SQL Server.

The way the expression is written matters. Using the implicit EXEC (as in the library)

rs = source_engine.execute("sp_columns @table_name = ?, @table_owner = ?", ('XXX', 'YYY'))

raises the exception seen above. However, using EXEC explicitly results in the correct behaviour (and should also be compatible with later SQL Server versions that do support the implicit form).

rs = source_engine.execute("EXEC sp_columns @table_name = ?, @table_owner = ?", ('XXX', 'YYY'))
rs.fetchone()

Have a nice day!