如何在IF中使用CREATE/DROP表

I am migrating data from a series of old data tables to new tables. In this process I came upon some issues.

我正在将数据从一系列旧数据表迁移到新表。在这个过程中，我遇到了一些问题。

I need to create a table, copy some data from old table to new one then drop old table. To achieve this, I need to write a script that does not show errors even if you run it on a migrated database, meaning even if the old tables are not there I should not see errors and simply skip the process.

我需要创建一个表，将一些数据从旧表复制到新表，然后删除旧表。为了实现这一点，我需要编写一个脚本，它不显示错误，即使您在迁移的数据库上运行它，也不显示错误，这意味着即使旧的表不在那里，我也不应该看到错误，而只是跳过这个过程。

Following is what I came up with up to now:

以下是我到目前为止的想法:

IF NOT EXISTS(SELECT * FROM sys.objects WHERE Object_ID = Object_ID('Old_Table'))
    GOTO Migrated_Before

  -- Drop OldTable Indexes, PK's and FK's;
  -- CREATE newTable ....;
  -- Add Indexes, PK's, Fk's;
  -- INSERT INTO NewTable(someFields) FROM OldTable.Fields, OtherTable.Fields ...;
  -- DROP OldTable;

Migrated_Before:

Here is the issue, Drop and Create need GO to commit changes before continue to next stage otherwise, next command will fail, however putting GO between GOTO and Label will make label undefined for GOTO.

这里的问题是，在继续下一阶段之前，Drop和Create需要提交更改，否则，下一个命令将失败，但是在GOTO和Label之间的GO将使标签未定义为GOTO。

How can I force each process to run before continue to next one without writing IF multiple times?

如何在连续运行下一个进程之前强制每个进程运行而不编写多次?

Solution: I leave the solution here as reference for others. I added begin transaction before every statement that need to happen before proceeding to next statement, for example, create table or drop FK's. and also replaced goto and label with begin end (my first solution)

解决方案:我把解决方案放在这里供他人参考。我在每个需要发生的语句之前添加了开始事务，例如，创建表或删除FK。用start end(我的第一个解决方案)替换goto和label

3 个解决方案

#1

Try using begin transaction, commit instead of go

尝试使用begin事务，提交而不是go

#2

Try putting the CREATE, DROP and following 3 commands in a separate stored procedure and call that procedure after GOTO. This SP will contan the required GO statement. This may keep the scope of Label as it is.

尝试将CREATE、DROP和以下3个命令放在一个单独的存储过程中，并在GOTO之后调用该过程。这个SP将包含所需的GO语句。这可以保持标签的范围。

#3

I can run this code below without any "GO"

我可以运行下面的代码而不需要任何“GO”

if object_id('abc') is not null
    drop table abc
create table abc (asdf varchar(10))

if object_id('abc') is not null
    drop table abc
create table abc (asdf varchar(10))

if object_id('abc') is not null
    drop table abc
create table abc (asdf varchar(10))

Also, you can just check for object_id() of null instead of querying the sys.objects table.

此外，您可以检查object_id()为null，而不是查询sys。对象表。

#1