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
How to Insert Multiple Records and Get the Identity Value
Split Given String and Prepare Case Statement
How to Pass Parameters to Query in SQL (Excel)
Get the Default Values of Table Columns in Postgres
SQL Server: Should I Use Information_Schema Tables Over Sys Tables
Join Multiple Tables with Active Records
How to Perform a Group by on an Aliased Column in SQL Server
Concatenate Two Database Columns into One Resultset Column
Disable Rails SQL Logging in Console
Solution For: Store Update, Insert, or Delete Statement Affected an Unexpected Number of Rows (0)
How to Use Asp Variables in SQL Statement
What's the Proper Index for Querying Structures in Arrays in Postgres JSONb
Remove Duplicates Using Only a MySQL Query
Using Tuples in SQL "In" Clause