Search code examples
sqlsql-serverexistscreate-table

Drop and create table in one command

do you know why below code is wrong on ms sql server?

DROP TABLE [IF EXISTS] database.table1,
create table table1 (...)
Solution

  • That's SQL Server 2016 syntax. For earlier versions, you can use the EXISTS function to check whether your table exists in the sys.tables list and drop it if it does. Then create a new table.

    Somewhat like this:

    IF EXISTS (SELECT * FROM sys.tables WHERE name = 'table1' AND type = 'U') DROP TABLE table1;
CREATE TABLE table1
(
    ...
    ...
);