Advanced SQL: Variations and Different Use cases of T-SQL Insert Statement

In my previous article, I demonstrated:

  1. Insert the output of the table-valued function in the SQL table.
  2. Insert the output of the table-valued function that is created on the remote database server.

In this article, I am going to demonstrate:

  1. Copy data between two tables, created in a different schema.
  2. Copy data between two tables, created in different databases on the same server.
  3. Copy data between two tables created, in different databases resides the different server (Cross server query)

Transfer data between two tables created in a different schema.

To copy data between two tables created in a different schema, we must use the following syntax:

For demonstration, I am going to use the WideWorldImportors database. I want to copy all the records from the [Application].[People] table to the [dbo].[EmployeesWithoutLogonName] table that has LOGONNAME equal to ”NO LOGON.”

First, Let’s review data from the [Application].[People] table. To do that, execute the following query:

The following is a screenshot of the output:

Now, let’s create the [dbo].[EmployeesWithoutLogonName] table. To do that, execute the following query:

Now let us copy records from [Application].[People] to [dbo].[EmployeesWithoutLogonName]. To do that, execute the following query:

Once data is inserted, execute the following query to verify that data has been copied.

The following is the output:

Copy data between two tables, created in a different database

As I explained above, we can copy data between tables created in two different schemas. Similarly, we can copy data between two tables created in two different databases. To copy data between two databases, the user must have the “db_datareader” permission on the source database and “db_datawriter” on the destination database.

Following is the syntax:

Now to demonstrate, I have created a new database named “HR.” To create a database, execute the following command.

Now I want to copy data of the EmployeesWithoutLogonName table, created in WideWorldImportors database to the “Employees” table created in the HR database.

First, let’s create a table named “Employees” in the HR database. To do that, execute the following query:

Now to copy data from the “EmployeesWithoutLogonName” table to the “EMPLOYEES” table, let us execute the following query:

Once data is inserted, execute the following query to verify that data has been copied.

The following is the output:

Copy data between two tables, created in the different databases on different servers

Now, similarly, we can copy data between two tables created on two separate databases created on two different servers. This can be performed using Linked Server or the OPENROWSET keyword.

The following is the syntax to connect SQL database created on the remote server using Linked Server.

In this demo, I am going to copy data of Employee table created on the Employees database to SQL database created on Azure cloud. To do that, firstly create a database on Azure SQL Instance. I have created a SQL server resource pool named “companyemployees.database.windows.net.

Now to create an HR database on Azure database instance, open SQL Server management studio. In the server name text box, select “companyemployees.database.windows.net.” We will use SQL Server authentication to connect SQL instance. To do that, select “SQL Server authentication” in the authentication type drop-down box. Provide appropriate user name and password and click on connect. See the following screenshot.

Now to create the “Cloud_HR_DB” database, Press Ctrl+N to open query editor window and execute the following command.

See the following image.

Once the database is created, execute the following query to create the “cEmployees” table on the Cloud_HR_DB database. To do that, execute the following query in the HR database.

Once the database and table are created, we need to create a linked server to perform a cross-server query. To create a linked server between Local SQL server instance and Azure SQL server instance, execute following command on Local SQL Server instance.

Once linked server is created, lets export data of the Employees table, created on the local instance of SQL server to the cEmployees table created on the Azure SQL Server Instance. To do that, execute the following query on local SQL Server instance:

Once data is exported from a local instance to Azure Instance, execute the following query to verify that data has been copied.

The following is the output:

We can also copy data to a remote server without using Linked Server. To do that, we must use the OPENROWSET keyword. OPENROWSET is an ad-hoc method to connect and access remote data source using OLEDB. For further reading of OPENROWSET, refer to Microsoft documentation of OPENROWSET.

Now in the following example, I will copy data from the cEmployees table created on Cloud_HR_DB to the Employees database created on the local instance. We will copy only those records which have Email_Address like” contoso.com.

Now to copy data, create a table named “ContosoEmployees” in the “HR” database. To do that, execute the following query:

Now to insert data using OPENROWSET, we need to enable the “Ad Hoc distributed queries” advanced option. To do that, execute the following command.

Now to copy data from the cEmployees table of the Cloud_HR_DB (Azure Instance) database to “ContosoEmployees” in the “Employees” database (Local Instance), execute the following query on the local server:

Once data is exported from a local instance to Azure Instance, execute the following query to verify that data has been copied.

The following is the output:

In this article I have explained how to:

  1. Copy data between two tables created in a different schema.
  2. Copy data between two tables created in different databases on the same server.
  3. Copy data between two tables created in different databases of the different server (Cross server query).

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
Nisarg Upadhyay

Leave a Reply

Your email address will not be published. Required fields are marked *