Inserting data from a select

Posted On: In: Microsoft SQL Server

In SQL Server it is possible to insert data from one table into another using a select within the insert statement, I recently ran into an issue where a stored procedure has been written that didn’t specify the columns on the insert side of the statement and simply specified

I want to demonstrate why this might cause a problem.

Build the tables

t1 which will hold all of the demo data to begin with

t2, similar to t1 but this will hold all of the data after the Insert, previous owner is however omitted (for this demo this is important)

Insert all the data

t1 is populated with the dummy data – I made all of this data up.

Insert on the select

When I created t1 I purposely didn’t create the previous owner column, remember? This script is going to attempt to Insert the data into t2 based on the Select from t1, the columns are specified on the Insert, however the Select is going to return all of the columns from t1 and attempt to insert them into t2

As expected, SQL Server has thrown an error;

To fix this, the columns need to be specified on both the Insert & Select which will tell SQL Server “Hey, I have these columns where I would like to get data from, and here are the columns I would like to put that data”

This is demonstrated in the below;

Just as we would expect, the data was successfully inserted.

Drop them tables

Just to keep things nice and tidy drop them temp tables.

To Conclude

If you have a table that you would like to Select data from and Insert that data into another table it is important to specify the columns on both sides of the statement (Select & Insert) to ensure that SQL server knows exactly where that data needs to be Inserted into otherwise it will have selected more data than there is room for it to Insert.