Create Table If Not Exists Equivalent in SQL Server

Check if table exists and if it doesn't exist, create it in SQL Server 2008

Something like this

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[YourTable](
....
....
....
)

END

Azure database: create table if not exists

You can't create tables in the master database in Azure. Please create a database and run your create table script there.

Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TheSchema'
AND TABLE_NAME = 'TheTable'))
BEGIN
--Do Stuff
END

How do I create a table based on another table

There is no such syntax in SQL Server, though CREATE TABLE AS ... SELECT does exist in PDW. In SQL Server you can use this query to create an empty table:

SELECT * INTO schema.newtable FROM schema.oldtable WHERE 1 = 0;

(If you want to make a copy of the table including all of the data, then leave out the WHERE clause.)

Note that this creates the same column structure (including an IDENTITY column if one exists) but it does not copy any indexes, constraints, triggers, etc.



Related Topics



Leave a reply



Submit