(678) 835-8539 info@key2consulting.com


Andy Kim, Business Intelligence Consultant at Key2 Consulting

By: Andy Kim

 

Introduction

The SELECT…INTO statement is an excellent tool to have in your arsenal if you’re a SQL developer. The SELECT…INTO statement:

  1. Creates a target table for you automatically, eliminating the need to manually create a table.
  2. Loads the target table lightning-fast through the use of minimal logging (under the correct conditions).

Alternatively, you could use the CREATE TABLE syntax to create the target table first, then use an insert statement to load it separately. This method can be much, much, slower – especially as the size of the data set grows larger (millions to billions of records). This can be the only option in some cases, however.

Although it is great that the SELECT…INTO statement automatically creates the target table for us, we lose a certain level of control on how the target table is created and defined. Certain tasks require the target table to be in a specific format in order to perform subsequent operations on it. Some examples of this might be but are not limited to:

  1. A column must be non-nullable in order to add a primary key constraint on it.
  2. An ALTER SWITCH statement requires the source and target table/column properties to be almost exactly the same.

Do we have any control over how the target table is created when using a SELECT…INTO statement? It turns out there are some ways we can manipulate the target table. I would like to demonstrate some of those manipulations in this blog post.

Let’s take a good look at the source table we will be extracting from. It will be the control table for our upcoming experiments. In particular, pay attention to the column names, data types, and nullability.

Source Table Image Key2 Consulting

What happens when we execute the following statement? How is the OrderDetail_Temp table created?

If we take a look in object explorer, we can see that it was created based on the original table, and in fact, matches the source at this level of detail.

Source Table 2 Key2 Consulting
 

Changing Column Names in the Target Table

The first simple way you can manipulate the target table is to set a column name. You can achieve this by aliasing a column in the query as shown below.

After running this statement:

We can see that the target table is created with the aliased column names:

 

Creating New Data Types in the Target Table

Another simple way you can manipulate the target table is to cast or convert to another data type. You can achieve this by casting/converting a column in the query, as shown below.

After running this statement:

We can see that the target table is created with the specified data types:

Target Table 2 Key2 Consulting

When casting or converting, we will need to keep in mind that our data may limit our options. In addition, we will need to understand the implications, such as losing precision in some cases.
 

Controlling the Nullability Property of Columns

You can use the NULLIF statement to create a nullable column, while you use an ISNULL statement to create a non-nullable column, as shown below.

After running this statement:

We can see that the target table is created with the specified nullabililty:

Target Table Nullability Key2 Consulting
 

Avoid Bringing Identity Property to the Resulting Table

Although we can’t see it from the screenshots, there is an identity on the source table on column RecordID. The identity is being carried over to the new table.

New Table Column Properties Key2 Consulting

A common hack to avoid bringing an identity property over to the target table is to do a dummy join or union. The dummy join or union causes the identity property to not be carried over.

This statement will create an identity on the RecordID column in the target table:

While this statement will NOT create the identity because there is a join:

column false (1)
 

Adding an Identity Column to the Resulting Table

Let’s say we want to add a new identity column in the target table. We can do it like this:

We can see that the target table has a new identity column.

Target Table 5 Key2 Consulting

Target Table 6 Key2 Consulting
 

Directing Your Table to Be on a Specific Filegroup

With SQL Server 2017, you can now also use the ON clause to direct the target table to be created on a specific file group other than the default.

 

Conclusion

Now that we have seen some different ways to manipulate the target table when using a SELECT…INTO statement, I encourage you to try these out next time the opportunity arises. I would also love to hear about other manipulations on the SELECT…INTO statement that you have used. Please leave a comment!
 

References

Select Into Statement General Information
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-into-clause-transact-sql?view=sql-server-2017

Select Into a Specific Filegroup
https://www.mssqltips.com/sqlservertip/5018/selectinto-enhancements-in-sql-server-2017/
 

Questions?

Thanks for reading! We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, (Star Wars), or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
 

Keep your data analytics sharp by subscribing to our mailing list

Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!

 


 

Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications. 

Share
Tweet
Share
Reddit