Scripts to Automate Creating and Loading a SQL Server Table

By: Rick Dobson   |   Updated: 2021-03-03   |   Comments (3)   |   Related: > TSQL

Problem

I work at a large company where different departments have one or more different SQL Server versions installed and operating within them. Please illustrate different T-SQL strategies for dropping, creating, and populating tables in custom schemas across different SQL Server versions from SQL Server 2019 back through SQL Server 2005.

Solution

A relatively common task for a SQL Server developer DBA is to create and populate a fresh copy of a table based on data from another data source. This tip illustrates four different approaches to achieving this task where the source data and a destination table are in different custom schemas.

Because of SQL Server’s continually evolving feature sets with successive versions, the best approach for a more recent version of SQL Server may not work in a less recent SQL Server version. For example, custom schemas were not available until SQL Server 2005. On the other hand, the drop table if exists statement was not available until SQL Server 2016. Also, highly differentiated catalog views for easy searches about the existence of different types of database objects, such as tables, in custom schemas evolved from SQL Server 2005 through SQL Server 2019. Easy searches for the existence of tables are important because T-SQL code returns SQL Server errors when the code attempts to drop a table that does not exist already or create a fresh version of a table that already exists.

This tip provides a set of scripts that function in one or more different SQL Server versions for copying data from one custom schema to another. When you are confronted with the need to implement this task for different versions of SQL Server, these alternative solutions will likely be very convenient to have available.

Initializing source data tables and a target schema for demonstrating T-SQL code

The script below sets up for the demonstration of four different approaches on how to drop a table and create a fresh version of the table in one custom schema where the source data reside in anther custom schema. The main purpose of this section’s script is to create two sample tables in a custom schema. One of these tables is used in the demonstration of each of the four approaches. The other table provides another data source for you to test the solution approaches with a different table than the one demonstrated in the tip. The comment at the top of the script references the prior creation of a database named object_drop_and_create. MSSQLTips.com published two prior tips on how to create a database with either SSMS or T-SQL code. Successive code blocks within the script start with a step number in a comment for easy reference.

• Step 1 shows a use statement to make the object_drop_and_create database the default database for the script.
• Step 2 conditionally drops prior versions of two tables (StateProvinces and Cities) in the source_data schema. After conditionally dropping the tables, conditionally drops and re-creates a fresh version of the source_data schema. This initializes the object_drop_and_create database to a known state.
• Step 3 illustrates an application of the select into statement to create and populate a table named StateProvinces in the source_data schema. The select statement extracts rows from the StateProvinces table in the Application schema of the Microsoft WideWorldImporters sample database. The select statement’s where clause criterion restricts the extracted rows to those for the USA (CountryID = 230).
• Step 4 demonstrates an extension of the same approach for populating the Cities table in the source_data schema based on an extract from the Cities table in the Application schema of the Microsoft WideWorldImporters sample database. The Cities table from the source_data schema is the table used in the remaining sections of this tip on how to conditionally drop and create fresh versions of tables within SQL Server.
• The script closes in step 5 with a create schema statement that creates an empty version of the conditional_table_demo schema. There are actually two steps to creating an empty version of the conditional_table_demo schema.
  • The step begins by conditionally dropping the Cities table in the schema. The demonstrations for different approaches to creating a fresh version of table in the conditional_table_demo schema ends with a fresh version of the Cities table in the conditional_table_demo schema.
  • After conditionally dropping the Cities table, the code conditionally drops the conditional_table_demo schema.
  • The go keyword precedes the create schema statement to meet the requirement for a create schema statement to be the first statement in a code block.

-- create object_drop_and_create database with New Database 
-- command in Object Explorer; then make new database
-- the default database
 
--step 1:
use object_drop_and_create
go
 
--step 2:
drop table if exists source_data.StateProvinces
go
drop table if exists source_data.Cities
go
drop schema if exists source_data
go
create schema [source_data] authorization [dbo]
go
 
--step 3:
-- StateProvince in USA; CountryID value of 230 is for USA
-- create and populate StateProvince in source_data schema
select 
StateProvinceID
,StateProvinceCode
,StateProvinceName
into source_data.StateProvinces
from [WideWorldImporters].[Application].[StateProvinces]
where CountryID = 230
 
-- echo StateProvinces
select * from source_data.StateProvinces

--step 4:
drop table if exists [source_data].[Cities]
go
 
-- Cities in USA
select 
CityID
,CityName
,Cities.StateProvinceID
into source_data.Cities
from [WideWorldImporters].[Application].[Cities]
inner join
(
-- StateProvince in USA; CountryID value of 230 is for USA
select 
StateProvinceID
,StateProvinceCode
,StateProvinceName
from [WideWorldImporters].[Application].[StateProvinces]
where CountryID = 230
) USA_StateProvince_ID
on Cities.StateProvinceID = USA_StateProvince_ID.StateProvinceID

-- echo Cities
select * from source_data.Cities

--step 5:
-- drop Cities table in conditional_table_demo schema
drop table if exists conditional_table_demo.Cities
-- add new schema
drop schema if exists conditional_table_demo
go
create schema conditional_table_demo authorization [dbo]

How dropping and creating tables can generate SQL Server errors

The screen shot below shows a script which demonstrates how SQL Server errors can result from the improper use of drop table and create table statements.

• If you attempt to drop a table that does not exist, then SQL Server throws an error.
  • The drop table if exists statement circumvents this kind of error by only attempting to drop tables that already exist. However, the statement only became available with SQL Server 2016. Therefore, you’ll need another approach for testing if a table exists already when using an older SQL Server version.
  • The drop table statement can drop a table that already exists, but it would throw an error if the table referenced in the statement does not exist.

• If you invoke a create table statement with a table name that is already in a database, then SQL Serve throws an error. If you need a new version of a table that already exists in a database, then you must first drop the old version of the table.

The following screen shot illustrates guidelines and outcomes for different attempts at dropping tables and creating new tables. The top pane shows the script, and the bottom Messages tab shows the resulting SQL Server messages from running the script.

• Step 1 makes the object_drop_and_create database the default database.
• Step 2 invokes the drop table if exists statement on line 8 and is followed by a drop table statement on line 13.
• Step 3 invokes a create table statement starting in line 19. Then, the step invokes the exact same create table statement starting in line 29.

Two error messages from running the script appear in the Messages tab.

• When the drop table if exists statement executes on line 8, one of two outcomes is possible.
  • If the Cities table exist in the conditional_table_demo schema, then the Cities table is dropped from the schema and control passes to the next statement in the script.
  • If the Cities table does not exist in the conditional_table_demo, then the statement passes control to the next statement without attempting to drop the Cities table.
• When a drop table statement like the one on line 13 executes, one of two possible outcomes is possible.
  • If the Cities table exist in the conditional_table_demo schema, then the Cities table is dropped from the schema.
  • In the current script, the drop table on line 13 always throws an error because the drop table if exists statement on line 8 drops any previously existing Cities table in the schema. The message has an error number of 3701.
• When the script invokes the first create table statement starting on line 19, the statement executes successfully. This is because the drop table if exists statement on line 8 removes any prior version of the Cities table in the schema.
• When the script invokes the second create table statement starting on line 29, an error is generated. This is because the Cities table in the conditional_table_demo schema already existed from the successful execution of the create table statement starting on line 19.
  • The error number is 2714.
  • The error message confirms that the cause of the error is because the Cities table already exists in the database.

Creating a fresh version of a table in SQL Server 2016 and more recent versions

This section presents a script for creating a fresh version of a table in SQL Server 2016 as well as more recent versions. The code was unit tested with SQL Server 2019. Because the 2019 version of SQL Server is more recent than the 2016 version, the use of the drop table if exists statement is permitted.

From the previous section, you should understand that SQL Server 2016 and more recent versions require just two statements to create a fresh version of a table: drop table if exists and create table. While this is true in a narrow sense, a typical developer DBA will often need to populate a newly created table. This script’s code uses an insert into…select statement to populate the table. Additionally, some minimal amount of unit testing will sometimes be required to confirm the code works as intended. The script in this section lists the tables in the conditional_table_demo schema before and after the create table and insert into…select statements.

The sample script divides the code into four steps.

• Step 1 drops a table named Cities in the conditional_table_demo schema if the table exists already.
• Step 2 enumerates the tables in the conditional_table_demo schema based on an inner join of sys.tables and sys.schemas by schema_id. The system catalog views named sys.tables and sys.schemas return one row each of metadata, respectively, for each table and schema within a SQL Server database. The select statement’s where clause within the select statement for this step restricts the metadata to the conditional_table_demo schema.
• Step 3 creates and populates the Cities table in the conditional_table_demo schema.
  • A create table statement creates the table.
  • An insert into…select statement populates the table from the Cities table in the source_data schema.
• Step 4 enumerates the tables in a database in the conditional_table_demo schema. By contrasting the output from this step with the output from step 2, you can assess the impact of the overall script, which is to create a fresh version of the Cities table in the conditional_table_demo schema.

-- this statement runs whether or not the Cities table
-- resides in the conditional_table_demo schema
-- works for sql server 2016 and beyond
-- the code illustrates how to conditionally drop tables 
-- with the drop table if exists statement
 
--step 1:
drop table if exists conditional_table_demo.Cities
go
 
--step 2:
-- list tables in conditional_table_demo schema
-- after dropping conditional_table_demo.Cities
select 
 'after dropping conditional_table_demo.Cities' [when]
select 
 tables.name [table name]
,schemas.name [schema name]
from sys.tables
inner join sys.schemas
on tables.schema_id = schemas.schema_id
where schemas.name = 'conditional_table_demo'
 
--step 3:
-- this statement creates a fresh empty copy of the
-- Cities table in the [conditional_table_demo] schema
create table conditional_table_demo.Cities(
   [CityID] [int] NOT NULL,
   [CityName] [nvarchar](50) NOT NULL,
   [StateProvinceID] [int] NOT NULL
) ON [PRIMARY]
go
 
-- populate Cities in conditional_table_demo schema
insert into conditional_table_demo.Cities
select * from source_data.Cities
 
--step 4:
-- list tables in conditional_table_demo schema
-- after inserting conditional_table_demo.Cities
select 
 'after inserting conditional_table_demo.Cities' [when]
select 
 tables.name [table name]
,schemas.name [schema name]
from sys.tables
inner join sys.schemas
on tables.schema_id = schemas.schema_id
where schemas.name = 'conditional_table_demo'

The following screen excerpt shows the pair of results sets from steps 2 and 4.

• The results set from step 2 appears first. Two separate queries are reported from step 2.
  • The when column value is initially "after dropping conditional_table_demo.Cities". This denotes when step 2 was executed.
  • The table name and schema name column values are both initially null. This is because the values are reported immediately after dropping the Cities table from the conditional_table_demo schema.
  • The next when column value is "after inserting conditional_table_demo.Cities ". This denotes when step 4 was executed.
  • The table name and schema name column values in the last results set are, respectively, Cities and conditional_table_demo. This is because the Cities table in the conditional_table_demo schema was created (and populated) in step 3.

Creating a fresh version of a table in a SQL Server after 2016 and before 2005

This section presents a script appropriate for SQL Server versions after 2016 and before 2005. The script is for creating a fresh version of a table. The script in this section also has four steps just like the script in the immediately preceding section. Furthermore, step 1 is the only one of the 4 steps that is different between the two scripts. For your easy reference, the full script for this section appears below. The text in this section following the full script listing drills down on the differences between step 1 in the script for this section and the previous section.

-- this statement runs whether or not the Cities table
-- resides in the conditional_table_demo schema
-- works for sql server versions after 2016 and before 2005
-- the code tracks schemas and tables with 
-- sys.schemas and sys.tables
 
--step 1:
if exists
(
select 
 schemas.name [schema name] 
,tables.name [table name]
from sys.tables
inner join sys.schemas
on tables.schema_id = schemas.schema_id
where 
 schemas.name = 'conditional_table_demo'
 and tables.name = 'Cities'
)
drop table conditional_table_demo.Cities

--step 2:
-- list tables in conditional_table_demo schema
-- after dropping conditional_table_demo.Cities
select 
 'after dropping conditional_table_demo.Cities' [when]
select 
 tables.name
,schemas.name
from sys.tables
inner join sys.schemas
on tables.schema_id = schemas.schema_id
where schemas.name = 'conditional_table_demo'
 
--step 3:
-- this statement creates a fresh empty copy of the
-- Cities table in the [conditional_table_demo] schema
create table conditional_table_demo.Cities(
   [CityID] [int] NOT NULL,
   [CityName] [nvarchar](50) NOT NULL,
   [StateProvinceID] [int] NOT NULL
) ON [PRIMARY]
go
 
-- populate Cities in conditional_table_demo schema
insert into conditional_table_demo.Cities
select * from source_data.Cities
 
--step 4:
-- list tables in conditional_table_demo schema
-- after inserting conditional_table_demo.Cities
select 
 'after inserting conditional_table_demo.Cities' [when]
select 
 tables.name
,schemas.name
from sys.tables
inner join sys.schemas
on tables.schema_id = schemas.schema_id
where schemas.name = 'conditional_table_demo'

Recall that the objective of step 1 is to conditionally drop the Cities table from the conditional_table_demo schema in the database. The code needs to be different because the script in the preceding section conditionally drops the Cities table with a drop table if exists statement. However, this statement type is only available in SQL Server 2016 and more recent versions, such as SQL Server 2019, which is the one used in this tip.

For SQL Server versions after 2016, other code is required to conditionally drop a table. This tip uses the sys.tables and sys.schemas catalog views in this section to check if a table exists in a schema before attempting to drop it. Recall from the "How dropping and creating tables can generate SQL Server errors" section that the drop table can fail if the table to be dropped does not already exist in the database.

The immediately preceding script embeds a query within an if exists statement.

• The embedded query returns one row or no rows depending on if a specific table (Cities) exists within a schema (conditional_table_demo).
  • The embedded query returns no rows when the Cities table does not exist in the conditional_table_demo schema.
  • The embedded query returns one row when the Cities table does exist in the conditional_table_demo schema.
• The if exists statement within the script for this section operates like an if…else statement.
  • If the embedded query returns one row, then the drop table statement after if exists is executed.
  • If the embedded query returns no rows, the control passes to the statement after the drop table statement.

The Results pane from the script in this section is the same as the Results pane in the preceding. Therefore, the Results pane is not also presented or described in this section.

Creating a fresh version of a table in SQL Server 2005

Around the time SQL Server 2005 was released, information_schema views were introduced to SQL Server for gathering metadata about database objects. Both information_schema views and system catalog views can collect metadata about database objects. There are about twenty information_schema views -- each for a different collection of database objects. While the syntax and metadata outcomes for information_schema views do not perfectly match system catalog views, such as sys.tables and sys.schemas, some developers may prefer collecting metadata via information_schema views.

The information_schema.tables view is especially relevant to this tip because it can return both table names and their corresponding schemas from a single view (no need to code a join between sys.tables and sys.schemas). This feature improves the readability and maintainability of the code for verifying if a table is present or absent in a database as a guide to execute or bypass a drop table statement.

As in the preceding examples for how to create a fresh copy of a table in one schema based on a source table in another schema, the following script has four sections. The information_schema.tables view is applied in steps 1, 2, and 4.

• Notice that the information_schema.tables view returns both table and schema names from a single view.
• Aside for gathering metadata on table names and their corresponding schemas, the overall code logic is the same as in previous scripts for creating and populating a fresh table. The four steps in the script have the following functions.
  • Step 1: Drop a prior table if it already exists in a schema.
  • Step 2: List the tables in the conditional_table_demo schema before creating and populating the fresh table version.
  • Step 3: Create and populate the fresh table version.
  • Step4: List the tables in the conditional_table_demo schema after creating and populating the fresh table version.

-- the information_schema view for tables is another way
-- for getting metadata about tables and schemas 
-- that was sometimes used in sql server 2005
-- this application of the information_schema.tables view
-- runs whether or not the Cities table
-- resides in the conditional_table_demo schema
 
--step 1:
if exists
(
select 
 table_schema [schema name] 
,table_name [table name]
from information_schema.tables
where 
 table_schema = 'conditional_table_demo'
 and table_name = 'Cities'
)
drop table conditional_table_demo.Cities
go
 
--step 2:
-- list tables in conditional_table_demo schema
-- after dropping conditional_table_demo.Cities
select 
 'after dropping conditional_table_demo.Cities' [when]
select 
 table_name [table name]
,table_schema [schema name]
from information_schema.tables
where table_schema = 'conditional_table_demo'
 
--step 3:
-- this statement creates a fresh empty copy of the
-- Cities table in the [conditional_table_demo] schema
create table conditional_table_demo.Cities(
   [CityID] [int] NOT NULL,
   [CityName] [nvarchar](50) NOT NULL,
   [StateProvinceID] [int] NOT NULL
) ON [PRIMARY]
go

-- populate Cities in conditional_table_demo schema
insert into conditional_table_demo.Cities
select * from source_data.Cities

--step 4:
-- list tables in conditional_table_demo schema
-- after inserting conditional_table_demo.Cities
select 
 'after inserting conditional_table_demo.Cities' [when]
select 
 table_name [table name]
,table_schema [schema name]
from information_schema.tables
where table_schema = 'conditional_table_demo'

This section shows the results sets from the preceding script because it uses a different process for metadata discovery than in the preceding two scripts. However, the results for the immediately preceding script are the same as in the prior two sections. You can verify this for yourself by matching the screen shot below with the screen shot for output in the "Creating a fresh version of a table in SQL Server 2016 and more recent versions" section.

Creating a fresh version of a table with a try…catch approach

The "How dropping and creating tables can generate SQL Server errors" section in this tip illustrates how errors can result from drop table and create table statements. The following script traps SQL Server errors that result from failed drop table and create table statements. The error traps are implemented with try…catch statements. You can learn more about try…catch statements from this prior MSSQLTips.com tip.

As with scripts from the prior three sections, there are four steps in the script.

• Step 1 wraps a drop table statement in the try clause of a try…catch statement.
  • If the drop table statement runs without generating an error, control passes to step 2, which lists the tables in the conditional_table_demo schema.
  • If the drop table statement fails, control passes to the catch clause. Print statements in the catch clause send information about the error to the messages tab of SSMS.
    • The first print statement is meant to provide custom feedback about the error.
    • The remaining print statements within the catch clause provide standard feedback about an error from SQL Server functions (such as error_number).
  • After the catch clause completes, control passes to step 2.
• Step 3 wraps a create table statement in the try clause of a try…catch statement. The try…catch statement in step 3 handles errors from the create table statement similarly to errors from the drop table statement in step 1.
• The advantages of the try…catch statement is that it can document errors if they occur, but it can also run to a successful conclusion if they do not occur.

-- this script runs whether or not the Cities table
-- resides in the conditional_table_demo schema at its start
-- the script starts by attempting to drop unconditionally
-- within a try...catch statement
-- the Cities table in the conditional_table_demo schema
-- later the script attempts to create the Cities table
-- in the conditional_table_demo schema
-- within a try...catch statement

--step 1:
begin try
   drop table [conditional_table_demo].[Cities]
end try
begin catch
   print 'trapped drop table error message:' 
   print error_message()
   print error_line()
   print error_number()
end catch
go
 
--step 2:
-- list tables in conditional_table_demo schema
-- after dropping conditional_table_demo.Cities
select 
'after dropping conditional_table_demo.Cities' [when]
select 
table_name [table name]
,table_schema [schema name]
from information_schema.tables
where table_schema = 'conditional_table_demo'
 
--step 3:
-- this statement creates a fresh empty copy of the
-- Cities table in the [conditional_table_demo] schema
begin try
create table conditional_table_demo.Cities(
   [CityID] [int] NOT NULL,
   [CityName] [nvarchar](50) NOT NULL,
   [StateProvinceID] [int] NOT NULL
) ON [PRIMARY]
end try
begin catch
   print 'trapped create table error message:' 
   print error_message()
   print error_line()
   print error_number()
end catch
go

-- populate Cities in conditional_table_demo schema
insert into conditional_table_demo.Cities
select * from source_data.Cities

--step 4:
-- list tables in conditional_table_demo schema
-- after inserting conditional_table_demo.Cities
select 
'after inserting conditional_table_demo.Cities' [when]
select 
table_name [table name]
,table_schema [schema name]
from information_schema.tables
where table_schema = 'conditional_table_demo'

The following screen shot shows the output from step 1 when there was a successful attempt to drop the Cities table in the conditional_table_demo schema.

The next screen shot shows an immediate attempt to re-run the code in the preceding screen shot. As you can see, this second attempt to run the code in step 1 fails by resulting in a 3701 SQL Server error. You can adjust the feedback by altering the text in the first print statement and by adding or deleting error processing functions in other print statements, such as error_message() or error_line().

Overview of the different approaches

Which of the four different approaches to drop, create, and populate fresh versions of tables in SQL Server is right for your requirements? Like a lot of SQL Server developer topics, the answer to the question is: it depends.

• Will your solutions only run on SQL Server 2016 and more recent versions of SQL Server? If so, then the drop table if exists statement represents an especially attractive formulation.
• This tip covers two additional formulations that depend on avoiding an error from trying to drop a table that does not exist. These two other solutions can run on less recent versions of SQL Server than 2016. The two other approaches differ primarily in how they discover if tables are present or not in a database.
• The try…catch formulation represents another interesting approach. This strategy does not try to stop an error from occurring. Instead, it traps the error after it happens. The syntax for this final approach is simple and easy to configure for those familiar with try…catch statements. If you are an experienced DBA developer, then it is likely that you have some prior exposure to try…catch statements.
• It is likely that some readers of this tip may have a favorite approach besides the four covered above for dropping, creating, and populating SQL Server tables. If so, please take a moment to reply with a comment which indicates why you prefer to switch to an approach described in this tip or stay with your current solution framework.

Next Steps

You can copy the code listings from the code windows in this tip to test the solutions that are of most interest to you. Alternatively, this link to a zipped download file allows you to download two .sql files with the code for this tip. Please recall that you need the Microsoft WideWorldImporters database loaded on your database server to run the samples as is. Recall also that you can gain some familiarity with editing the code in the code windows by referencing the StateProvinces table instead of the Cities table.

If you have a corporate database which you want to try with any of the solutions, consider modifying the provided code in at least two ways:

• Setup a custom schema named data_source within the database that you use as your default database. Populate the tables in the data_source schema with one or more of your own data sources.
• Setup another custom schema named conditional_table_demo. Make sure the default database is set to object_drop_and_create (or whatever other name you are using as your default database).

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips