Defaults and Using SELECT * INTO When Copying or Testing Functionality

SQL Server gives us a quick way to copy a table's data by using a SELECT * INTO command where the table following the INTO receives a copy of the selected data. This can be very useful in many contexts, as I list an example immediately at the start of the video SQL Basics: SELECT * INTO (Table Copy and Testing). Unfortunately, this syntax may not be useful in every situation where we need to test our data with functionality (the latter part depends on what type of functionality). There are advantages and disadvantages of explicit creation - the biggest deciding factor being whether we only need the data or we need the data and design.

Some discussion points and questions answered in the video:

• What is one of the first practical examples where we can use this development technique and why would this be useful?
• In the example shown, what do we see didn't copy over using this syntax? Why might this be important relative to what we're trying to accomplish?
• What is a type of testing where this copy syntax would be helpful? What is a type of testing where it would not assist us?
• What would be an example of a failure we might get or a problem we might see if we use this syntax in an inappropriate situation?
• What are some schema considerations we should review before using this syntax to create a copy? Why do these matter if we want to copy the design?

As an example of how this affected one situation: developers created a copy of the table to test data and functionality, yet were able to add data that was not enforced by a foreign key constraint. The result of their A-B testing ultimately failed because no constraints were copied with this syntax. While their data testing was valid, the functionality testing was invalid as records that would fail with the A test would pass with the B test. When we reviewed the compared results, the underlying issue involved how the data and schema were created - a quick copy over explicit creation with a copy.

For mastering data transformation from one form to another form, check out the highest-rated Automating ETL course on Udemy. For a coupon to the course, check out the trailer video on the channel SQL In Six Minutes.

Some of the testing discussion assumes we're testing in the same environment. In some contexts, we may have an A and B testing environment (ie: Sandbox A and Sandbox B). The advantage of these types of environments is that we can easily do A-B testing and avoid situations like the above discussion where we're trying to test side-by-side in the same environment on the same server within the same database. There are other techniques that are similar, which may save us time if testing is involved. Keep in mind that this doesn't apply to situations where we need a quick backup of the data: in these situations, copying the table will be much faster than doing a full restore, unless we need functionality saved along with the data. For instance, if our copy of data must still align with foreign key constraints, it's worth ensuring that the appropriate constraints remain even on the copy of data. It's entirely possible that a backup may miss latest data or may have data added by something else, relative to the design.

Are you looking for tech consultants that can assist with design and development? From building custom applications to working with existing infrastructure that's causing you trouble, we can get you connected to consultants who can assist. You can contact for assistance.

SQL In Six Minutes | Automating ETL | T-SQL In 2 Hours | Consumer Guide To Digital Security