Create a table if the database exists, if not create it in another database

Question

Create a table if the database exists, if not create it in another database

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I want to verify if there is a database BDDATOS1, if it exists I create a table in this database BDDATOS1, otherwise I create a table in database BDDATOS2.

When I run the following

   IF EXISTS(SELECT * FROM master.dbo.SYSDATABASES WHERE NAME = 'BDDATOS1')

    CREATE TABLE BDDATOS1.._articulos2 ([carticulos_id] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [cagrupacion_articulos_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [carticulos_nombre] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL );
   else 
    CREATE TABLE BDDATOS2.._articulos2 ([carticulos_id] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [cagrupacion_articulos_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [carticulos_nombre] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL );
end

gives me a pre-execution error saying that BDDATA1 does not exist, which is true, but it is assumed that it should go to the ELSE statement and create the table in database BDDATOS2.

This error only comes up with create, with UPDATE, SELECT and DROP it does not give me an error.

sql sql-server-2008-r2

asked by Pepe Benites 26.08.2017 в 18:30

source

2 answers

How to use Background image in a Materialize column Send data to a Laravel driver with AJAX

score 1 · Answer 1

No, you can not do it the way you think about it basically because the engine fully evaluates the script, and indeed the instruction CREATE TABLE BDDATOS1.._articulos2 is invalid if there is no BDDATOS1 .

One way to solve it is to not evaluate the creation statement that gives the error:

DECLARE @SQL NVARCHAR(2000)

IF EXISTS(SELECT * FROM master.dbo.SYSDATABASES WHERE NAME = 'BDDATOS1')
BEGIN

    SELECT @SQL = 'CREATE TABLE BDDATOS1.._articulos2 ([carticulos_id] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [cagrupacion_articulos_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [carticulos_nombre] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL );'

END ELSE BEGIN

    SELECT @SQL = 'CREATE TABLE BDDATOS2.._articulos2 ([carticulos_id] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [cagrupacion_articulos_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [carticulos_nombre] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL );'

END

EXEC sp_executesql @SQL

As you see, we set a @SQL sentence based on the existence of a database and execute it directly by: EXEC sp_executesql @SQL , this avoids the evaluation of the Script that gave us the error, but yes,% is evaluated @SQL with which we will not have problems because it is already built according to the corresponding database.

Note: The use of sp_executesql should be limited to specific and controlled situations as it can be a source of problems due to: 1) Injections of malicious code 2) Performance problems due to absences of the optimizations performed by the engine.

score 0 · Answer 2

You can not directly check the if query in that way. You should store the result and make the comparison with it.

I think something like this would help you:

Define @existe int
set @existe = 0
SELECT @exist= count(*) FROM master.dbo.SYSDATABASES WHERE NAME = 'BDDATOS1'
IF @exist > 0

CREATE TABLE BDDATOS1.._articulos2 ([carticulos_id] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [cagrupacion_articulos_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [carticulos_nombre] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL );
else 
CREATE TABLE BDDATOS2.._articulos2 ([carticulos_id] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [cagrupacion_articulos_id] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,  [carticulos_nombre] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL );
end