CODEWITHSUNDEEP
sql-servert-sql
Chaos Ryeze
Chaos Ryeze

Reputation: 88

"create if not exist" and "create table like" sql server query

how can i create a table just if that table is not exist. in my case, i want to create table with this query :

SELECT * 
INTO a
FROM b

that table a in db A and table b in db B. any help?

Upvotes: 6

Views: 8548

Answers (3)

Scaffold
Scaffold

Reputation: 597

MySQL, PostgreSQL, Sqlite:

CREATE TABLE IF NOT EXISTS t_exists1 
(
   c1 TYPE
);

Oracle:

   DECLARE cnt NUMBER;
   BEGIN
     SELECT count(*) INTO cnt FROM all_tables WHERE table_name = 'T_EXISTS1';
     IF cnt = 0 THEN 
       EXECUTE IMMEDIATE 'CREATE TABLE t_exists1 
         (
           c1 TYPE
         );'; 
     END IF;
   END;

SQL Server:

  IF OBJECT_ID('t_exists1', 'U') IS NULL
  CREATE TABLE t_exists1 
  (
       c1 TYPE
  );

Upvotes: 2

Steven Wexler
Steven Wexler

Reputation: 17269

You can use the OBJECT_ID function for SQL Server. 

IF OBJECT_ID('a') IS NULL
        SELECT *
        INTO a
        FROM b

Upvotes: 5

Krichevskoy
Krichevskoy

Reputation: 254

if not exists (select [name] from sys.tables where [name] = 'a') SELECT * 
INTO A.dbo.a
FROM B.dbo.b

you can try this .. its simple one.

Upvotes: 5

Related Questions