`CREATE TABLE AS SELECT` not valid SQL for SQL Server

[jimhester]

This idiom is used frequently by DBItest, but is not valid for SQL Server, where one needs to use SELECT INTO instead. Not sure how is best to handle this but it restricts the number of tests I can turn on for the SQL Server Driver with odbc.

dbSendStatement(con, "CREATE TABLE test AS SELECT 1 as a")
#>  Error in eval(substitute(expr), envir, enclos) : 
#>   <SQL> 'CREATE TABLE test AS SELECT 1 as a'
#>   nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'SELECT'.
dbSendStatement(con, "SELECT 1 AS a INTO test")
dbReadTable(con, "test")
#>   a
#> 1 1

[krlmlr]

Can we declare a tweak that contains a glue pattern that describes how to compose this query, and perhaps others?

[jimhester]

That seems like a good solution to me

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.