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:

INSERT INTO <DESTINATIONSCHEMANAME>.<DESTINATIONTABLENAME> 
            (                         COLUMN1, 
                        COLUMN2, 
                        COLUMN3                  .. 
            ) 
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3 
FROM   <SOURCESCHEMANAME>.<SOURCETABLENAME>

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:

SELECT [PERSONID], 
       [FULLNAME], 
       [PREFERREDNAME], 
       [SEARCHNAME], 
       [LOGONNAME], 
       [PHONENUMBER], 
       [FAXNUMBER], 
       [EMAILADDRESS] 
FROM   APPLICATION.PEOPLE 
WHERE  LOGONNAME = 'NO LOGON'

The following is a screenshot of the output:

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

CREATE TABLE EMPLOYEESWITHOUTLOGONNAME 
  ( 
     [ID]             INT IDENTITY(1, 1), 
     [FULL_NAME]      VARCHAR(500), 
     [PREFERRED_NAME] VARCHAR(500), 
     [SEARCH_NAME]    NVARCHAR(MAX), 
     [LOGON_NAME]     VARCHAR(250), 
     [PHONE_NUMBER]   VARCHAR(50), 
     [FAX_NUMBER]     VARCHAR(100), 
     [EMAIL_ADDRESS]  NVARCHAR(250) 
  )

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

INSERT INTO [DBO].[EMPLOYEESWITHOUTLOGONNAME] 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT [FULLNAME], 
       [PREFERREDNAME], 
       [SEARCHNAME], 
       [LOGONNAME], 
       [PHONENUMBER], 
       [FAXNUMBER], 
       [EMAILADDRESS] 
FROM   APPLICATION.PEOPLE 
WHERE  LOGONNAME = 'NO LOGON'

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

SELECT * 
FROM   EMPLOYEESWITHOUTLOGONNAME

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:

INSERT INTO <DESTINATIONDATABASENAME>.<DESTINATIONSCHEMANAME>.<DESTINATIONTABLENAME> 
            ( 
                        COLUMN1, 
                        COLUMN2, 
                        COLUMN3 .. 
            ) 
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3 
FROM   <SOURCEDATABASENAME>.<SOURCESCHEMANAME>.<SOURCETABLENAME>

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

CREATE DATABASE HR

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:

USE HR 
GO 
CREATE TABLE EMPLOYEES 
  ( 
     [ID]             INT IDENTITY(1, 1), 
     [FULL_NAME]      VARCHAR(500), 
     [PREFERRED_NAME] VARCHAR(500), 
     [SEARCH_NAME]    NVARCHAR(MAX), 
     [LOGON_NAME]     VARCHAR(250), 
     [PHONE_NUMBER]   VARCHAR(50), 
     [FAX_NUMBER]     VARCHAR(100), 
     [EMAIL_ADDRESS]  NVARCHAR(250) 
  )

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

INSERT INTO HR.DBO.EMPLOYEES 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT FULL_NAME, 
       PREFERRED_NAME, 
       SEARCH_NAME, 
       LOGON_NAME, 
       PHONE_NUMBER, 
       FAX_NUMBER, 
       EMAIL_ADDRESS 
FROM   WIDEWORLDIMPORTERS.DBO.EMPLOYEESWITHOUTLOGONNAME

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

SELECT * 
FROM   HR.DBO.EMPLOYEES

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.

INSERT INTO <LINKEDSERVERNAME>.<DESTINATIONDATABASENAME>.<DESTINATIONSCHEMANAME>.<DESTINATIONTABLENAME> 
            ( 
                        COLUMN1, 
                        COLUMN2, 
                        COLUMN3 .. 
            ) 
SELECT COLUMN1, 
       COLUMN2, 
       COLUMN3 
FROM   <SOURCEDATABASENAME>.<SOURCESCHEMANAME>.<SOURCETABLENAME>

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.

CREATE DATABASE CLOUD_HR_DB 
GO

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.

CREATE TABLE cEMPLOYEES 
  ( 
     [ID]             INT IDENTITY(1, 1), 
     [FULL_NAME]      VARCHAR(500), 
     [PREFERRED_NAME] VARCHAR(500), 
     [SEARCH_NAME]    NVARCHAR(MAX), 
     [LOGON_NAME]     VARCHAR(250), 
     [PHONE_NUMBER]   VARCHAR(50), 
     [FAX_NUMBER]     VARCHAR(100), 
     [EMAIL_ADDRESS]  NVARCHAR(250) 
  )

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.

USE [MASTER] 
GO 

EXEC MASTER.DBO.SP_ADDLINKEDSERVER 
  @SERVER = N'AZURE_SQL_SERVER', 
  @SRVPRODUCT=N'', 
  @PROVIDER=N'SQLNCLI', 
  @DATASRC=N'COMPANYEMPLOYEES.DATABASE.WINDOWS.NET', 
  @CATALOG=N'CLOUD_HR_DB' 

/* FOR SECURITY REASONS THE LINKED SERVER REMOTE LOGINS PASSWORD IS CHANGED WITH ######## */ 
EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN 
  @RMTSRVNAME=N'AZURE_SQL_SERVER', 
  @USESELF=N'FALSE', 
  @LOCALLOGIN=NULL, 
  @RMTUSER=N'NISARGUPADHYAY', 
  @RMTPASSWORD='########' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'DATA ACCESS', 
  @OPTVALUE=N'TRUE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'RPC', 
  @OPTVALUE=N'TRUE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'RPC OUT', 
  @OPTVALUE=N'TRUE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'CONNECT TIMEOUT', 
  @OPTVALUE=N'0' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'LAZY SCHEMA VALIDATION', 
  @OPTVALUE=N'FALSE' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'QUERY TIMEOUT', 
  @OPTVALUE=N'0' 
GO 

EXEC MASTER.DBO.SP_SERVEROPTION 
  @SERVER=N'AZURE_SQL_SERVER', 
  @OPTNAME=N'REMOTE PROC TRANSACTION PROMOTION', 
  @OPTVALUE=N'TRUE' 
GO

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:

INSERT INTO [AZURE_SQL_SERVER].Cloud_HR_DB.DBO.cEMPLOYEES 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT FULL_NAME, 
       PREFERRED_NAME, 
       SEARCH_NAME, 
       LOGON_NAME, 
       PHONE_NUMBER, 
       FAX_NUMBER, 
       EMAIL_ADDRESS 
FROM   HR..EMPLOYEES

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

SELECT * 
FROM [AZURE_SQL_SERVER].Cloud_HR_DB.DBO.cEMPLOYEES

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:

CREATE TABLE [DBO].[CONTOSOEMPLOYEES] 
  ( 
     [ID]             [INT] IDENTITY(1, 1) NOT NULL, 
     [FULL_NAME]      [VARCHAR](500) NULL, 
     [PREFERRED_NAME] [VARCHAR](500) NULL, 
     [SEARCH_NAME]    [NVARCHAR](MAX) NULL, 
     [LOGON_NAME]     [VARCHAR](250) NULL, 
     [PHONE_NUMBER]   [VARCHAR](50) NULL, 
     [FAX_NUMBER]     [VARCHAR](100) NULL, 
     [EMAIL_ADDRESS]  [NVARCHAR](250) NULL 
  ) 
GO

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

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE WITH OVERRIDE

EXEC SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES',1
RECONFIGURE WITH OVERRIDE

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:

USE HR 
GO 

INSERT INTO CONTOSOEMPLOYEES 
            ([FULL_NAME], 
             [PREFERRED_NAME], 
             [SEARCH_NAME], 
             [LOGON_NAME], 
             [PHONE_NUMBER], 
             [FAX_NUMBER], 
             [EMAIL_ADDRESS]) 
SELECT * 
FROM   OPENROWSET('SQLNCLI11', 
'SERVER=COMPANYEMPLOYEES.DATABASE.WINDOWS.NET,1433;DATABASE=CLOUD_HR_DB;UID=NISARGUPADHYAY;PWD=NISARG@8987'
, 
'SELECT FULL_NAME, PREFERRED_NAME, SEARCH_NAME, LOGON_NAME, PHONE_NUMBER, FAX_NUMBER, EMAIL_ADDRESS FROM   CEMPLOYEES WHERE  EMAIL_ADDRESS LIKE ''%CONTOSO.COM%''')A

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

SELECT FULL_NAME, 
       PREFERRED_NAME, 
       SEARCH_NAME, 
       LOGON_NAME, 
       PHONE_NUMBER, 
       FAX_NUMBER, 
       EMAIL_ADDRESS 
FROM   CONTOSOEMPLOYEES

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).