Create table but Drop it if the table exists already

Question

I am working on a request where I have to create a table to insert some data. So, obviously I will have first have a delete table st. before the create st. but when I am running this for the first time(before the table can be created) it will pop up an error saying table not created and then creates table and goe son from here. So every time any one runs my code for the first time it will pop up this error at drop table st. Does any one have any better idea??

Some thing like " if table exists then drop else create table"
I am not sure how are we going to do this in sql

Drop table table_name;--------------> here it throws an error for the first time saying table does not exist.

Create table table_name

{ so on };

By the way I am working on Teradata but a simple sql logic would help.

http://forums.teradata.com/forum/enterprise/drop-table-if-exists-0 — Joachim Isaksson, Feb 15 '12 at 18:49

score 11 · Accepted Answer · answered Feb 15 '12 at 19:45

You can create a stored procedure owned by SYSDBA or other admin level user with adequate DROP TABLE and CREATE TABLE privileges that does the following:

Check DBC.Tables to see if object exists.
If object exists, drop it.
Run the DDL to recreate the table: CREATE TABLE <TargetDB>.<TargetTable> AS <SourceDB>.<SourceTable> WITH DATA AND STATS;

You can make it more dynamic by accepting additional parameters on whether the data and/or stats should be copied to the new table.

If you are using BTEQ, you can do something similar (BTEQ command syntax may be a little off but close enough to get the point across):

SELECT 1 
FROM DBC.TABLES 
WHERE DatabaseName = '<TargetDB>'
  AND TableName = '<TargetTable>'
  AND TableKind = 'T' /* Make sure it is in fact a table, not a view, macro etc */


.IF ACIVITYCOUNT = 0 THEN GOTO CreateNewTable;

DROP TABLE <TargetDB>.<TargetTable>;

.IF ERRORCODE = 3807 THEN GOTO CreateNewTable; /* Table dropped by another process? */
.IF ERRORCODE > 0 THEN .QUIT ERRORCODE; /* Unexpected error */

.LABEL CreateNewTable;

CREATE <TargetDB>.<TargetTable> AS <SourceDB>.<SourceTable> WITH DATA AND STATISTICS;

score 1 · Answer 2 · edited Oct 26 '17 at 15:44

1

It seems SAS proc sql cannot do it like T-SQL directly. Anyway, you can write a macro to check if the data set exist. If so, drop it first. Then create the table. Like the following code.

%macro checkDrop(tmpData);
%if %SYSFUNC(exist(&tmpData)) %then %do;
    proc sql;
    drop table &tmpData;
    quit;
    %end;
    %else %do;
    proc sql;
    create table &tmpData (a numberic, b numberic);
    %end;
%mend;
%checkDrop(tmp)

edited Oct 26 '17 at 15:44

Schoyen

7
3

answered May 31 '16 at 09:19

Pihong Wu

11
1

FYI - This approach also works in SAS Enterprise Guide, at least the part about dropping tables if they exist (didn't need to create them). – CragMonkey Apr 20 '17 at 15:20

Create table but Drop it if the table exists already

2 Answers2

Linked