Data Cloning of a Table

SQL Insert Data Techniques

1. Introduction To Data Insertion

We all know that to insert data into SQL server database we use the Insert statement. This article points out entering data into the database table using various techniques possible. First, we will start with normal insert statements. Then we will go ahead with inserting data from existing tables in the SQL Server Database. This article requires Microsoft Pubs database.

2.Inserting All Column Values

Look at the below SQL statements. The first one Use Pubs; informs SQL server that it should perform the queries on the Pubs database. In the below query we are executing the insert statement on the Jobs table of the Pubs database. All we specified is the table name Jobs and some list of values separated by a comma between the open and close parenthesis.

The below picture shows columns definition of Pubs table:

Jobs Table From Pubs Database
Jobs Table From Pubs Database

In the above picture, the table Jobs has job_id column and we are not inserting the values in the above query for it. This is because that column is an auto populated key column. We discuss that in some other article. So in the Insert statement if we are not specifying the column names, then we are inserting values for all the columns exist in that table.

Below is the result of executing the SQL Statement:

Inserting data for all columns in the table
Inserting data for all columns in the table

3. Inserting Data for Specific Columns

Now look at the below insert statement. Here, after the table name discounts, column names separated by commas are specified within the parenthesis. We call this as a Column List. Next to it is the Value List. In the values list, we provide the values in the same order in which we provided column names in the column list. If the value is a text string, a single quote is used to enclose the string.

Below is the result of executing the Query:

Insert Into Specific Columns
Insert Into Specific Columns

4. ‘Insert Into … Select’ Technique

Before we go, first we will create a table for inserting the data using Insert into...Select technique. Below is the table create statement:

We just created a Distype  table with two columns. And we will insert data into this table from the existing Discounts table in the Pubs database. Now look at the below insert statement:

Here, we specified the target table DisType  as the insertion target. We also stated the column names from it. SQL Server uses these columns as insertion target. Instead of passing the values manually, we specified a source table to pick the data. As we are looking values for only two columns DiscountType, Discount in the target table DisType, we retrieve those two columns in the source table Discounts through a Select statement. Note, it does not require that column name should match. But we should match the data type for smooth insertion of data from source to destination table. The screenshot below shows new Table with data:

Insert Into with Select
Insert Into with Select

5. Bulk Insert by Creating Destination Table

Consider the below SQL statement. Even though the statement looks like a select statement, it performs two tasks.

  1. It first creates the destination table disTypeOneMore.
  2. It queries the source table Discounts and inserts the resultant data into the destination table disTypeOneMore.

The column names and data type of the Destination Table is taken from the select statement of the Source Table.

The Picture shows Data Clone of a Table:

Data Cloning of a Table
Data Cloning of a Table

Note: The examples here uses Microsoft supplied Pubs database.

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.