Create a table, but release it if the table already exists

Question

I am working on a query where I need to create a table to insert some data. So, it is obvious that first I will have a st deletion table. before creating st. but when I run it the first time (before the table can be created), it will display an error message without creating the table, and then create a table and a son from it. Therefore, every time someone runs my code for the first time, he throws this error in the drop st table. Does anyone have a better idea?

Something like "if the table exists, then leave another create table"
I'm not sure how we do it in sql

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

Create table table_name

{so on};

By the way, I'm working on Teradata, but simple SQL logic will help.

Answer 1

You can create a stored procedure owned by SYSDBA or another user at the administrator level, with the corresponding DROP TABLE and CREATE TABLE privileges, which perform the following actions:

• Check DBC.Tables to see if the object exists.
• If the object exists, leave it.
• Run 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 about whether data and / or statistics should be copied to a new table.

If you use BTEQ, you can do something similar (the syntax of the BTEQ commands may be a little bit, but close enough to get the point):

 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; 

Answer 2

Try: Drop table IF EXISTS table_name;

And then continue to create the table, as it will be guaranteed that it no longer exists.

Answer 3

It seems that SAS proc sql cannot do this as T-SQL directly. Anyway, you can write a macro to check if a data set exists. If so, drop it first. Then create a 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)