SQL 创建表但如果表已经存在则删除它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9299329/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create table but Drop it if the table exists already
提问by user1172117
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??
我正在处理一个请求，我必须创建一个表来插入一些数据。所以，显然我将首先有一个删除表 st。在创建圣之前。但是当我第一次运行它时（在可以创建表之前）它会弹出一个错误，说表未创建，然后创建表并从这里开始。因此，每次有人第一次运行我的代码时，它都会在 drop table st 处弹出此错误。有没有人有更好的主意？
Some thing like " if table exists then drop else create table"
I am not sure how are we going to do this in sql
诸如“如果表存在则删除其他创建表”之类的事情
我不确定我们将如何在 sql 中执行此操作
Drop table table_name;--------------> here it throws an error for the first time saying table does not exist.
Drop table table_name;--------------> 这里它第一次抛出一个错误，说表不存在。
Create table table_name
创建表 table_name
{ so on };
{ 很快 };
By the way I am working on Teradata but a simple sql logic would help.
顺便说一下，我正在研究 Teradata，但一个简单的 sql 逻辑会有所帮助。
回答by Rob Paller
You can create a stored procedure owned by SYSDBA or other admin level user with adequate DROP TABLEand CREATE TABLEprivileges that does the following:
您可以创建SYSDBA或有足够的其他管理员级别的用户拥有一个存储过程DROP TABLE和CREATE TABLE特权执行以下操作：
- 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;
- 检查 DBC.Tables 以查看对象是否存在。
- 如果对象存在，则删除它。
- 运行 DDL 以重新创建表：
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):
如果你使用 BTEQ，你可以做一些类似的事情（BTEQ 命令语法可能有点偏离，但足够接近以理解这一点）：
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;
回答by Pihong Wu
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.
似乎 SAS proc sql 不能直接像 T-SQL 那样做。无论如何，您可以编写一个宏来检查数据集是否存在。如果是这样，请先放下它。然后创建表。像下面的代码。
%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)
