Advanced SQL: Insert output of the parameterized table-valued function in SQL table

Total: 4 Average: 3.5

In this article, I am going to demonstrate the following:

  1. How to insert the output of a table-valued function in a SQL table.
  2. How to insert the output of a table-valued function which is created on the remote database server.

What is “Insert into” statement

In RDBMS, “Insert into” is one of the basic SQL statements. It is used to insert new records in a SQL table. Using the statement, we can perform the following tasks:

  • Insert new records in a table (Basic Insert).
  • Insert values of a specific column in a table.
  • Insert the output generated by a stored procedure in a SQL table.

To demonstrate the above, let’s create a table named “Students” on DemoDatabase. Execute the following code to create the table:

CREATE TABLE STUDENTS 
  ( 
     ID        	INT IDENTITY(1, 1) PRIMARY KEY, 
     FIRSTNAME 	VARCHAR(250), 
     LASTNAME  	VARCHAR(250), 
     ADMISSIONDATE  DATETIME,
     GRADE     	CHAR(1) 
  )

Perform basic insert

To perform basic insert, we need to provide the name of the target table and values of the table. The following is a basic syntax of the basic insert statement:

INSERT INTO <target TABLE NAME> VALUES 
            ( 
                        <value FOR COLUMN 1 >, 
                        <value FOR COLUMN 1 >.. 
            )

For example, we want to insert first name, last name, and grade of three students in the “Students” table. To do that, execute the following code:

INSERT INTO STUDENTS 
VALUES      ('NISARG', 
             'UPADHYAY', 
             '2018-09-11', 
             'A'), 
            ('RAGHAV', 
             'DATTA', 
             '2017-10-01', 
             'A'), 
            ('KIRAN', 
             'AMIN', 
             '2016-01-31', 
             'A')

Execute the “Select” query against“Student” to review the results.

SELECT FIRSTNAME, 
       LASTNAME, 
       ADMISSIONDATE, 
       GRADE 
FROM   STUDENTS

The result is as follows:

Insert values of a specific column in the table

To insert values in specific columns of a table, you need to provide the name of the target table and name of the columns in which you want to insert data. The following is the syntax.

INSERT INTO <TARGET TABLE NAME> 
            ( 
                        COLUMN 1 , 
                        COLUMN 2 
            ) 
            VALUES 
            ( 
                        <VALUE FOR COLUMN 1 >, 
                        <VALUE FOR COLUMN 1 >.. 
            )

For example, we want to insert first name and last name of two students in the “Students” table. To do that, execute the following code:

INSERT INTO STUDENTS 
            (FIRSTNAME, 
             LASTNAME) 
VALUES      ('NIMESH', 
             'UPADHYAY'), 
            ('RUPESH', 
             'DATTA')

Execute the “Select” query against the “Students” table.

SELECT FIRSTNAME, 
       LASTNAME, 
       ADMISSIONDATE, 
       GRADE 
FROM   STUDENTS

The output looks as follows:

Insert the output, generate by a stored procedure

To insert the output of a stored procedure in the table, we need to specify the target table name and the source stored procedure. To generate the output of the stored procedure, we need to use the “exec” or “EXECUTE” keyword. So, we need to provide the table name or names of the columns followed by the “exec” keyword. The following is the syntax:

INSERT INTO <TARGET TABLE NAME> 
            ( 
                        COLUMN 1 , 
                        COLUMN 2 
            ) 
EXEC <PROCEDURENAME>

For example, we want to insert the output of the procedure which populates the names of the students whose admission date is not null. To do that, we will create a stored procedure named “spGet_Student_AdmissionDate”. To create a stored procedure, execute the following code:

USE DEMODATABASE 

GO 

CREATE PROCEDURE SPGET_STUDENT_ADMISSIONDATE 
AS 
  BEGIN 
      SELECT ISNULL(FIRSTNAME, '') + ' ' 
             + ISNULL(LASTNAME, '') AS STUDENTNAME, 
             ADMISSIONDATE, 
             GRADE 
      FROM   STUDENTS 
      WHERE  ADMISSIONDATE IS NOT NULL 
  END

Once the procedure is created, run the procedure by executing the following code:

EXECUTE spGet_Student_Admissiondate

The output looks as follows:

As I mentioned above, we want to insert the output of the stored procedure named “spGet_Student_Admissiondate” in a temporary table. Firstly, execute the following code to create the table:

  ( 
     ID            INT IDENTITY(1, 1), 
     STUDENTNAME   VARCHAR(250), 
     ADMISSIONDATE DATETIME, 
     GRADE         CHAR(1) 
  )

Once the table is created, execute the following code to insert the output of “spGet_Student_Admissiondate” to “#TempStudents”.

INSERT INTO #TEMPSTUDENTS 
EXECUTE SPGET_STUDENT_ADMISSIONDATE 
Output: (3 rows affected)

Now let’s check the output of “#TEMPSTUDENTS”. To do that, execute the following code:

Now, as I mentioned above, I am going to demonstrate how can we insert an output generated by a table-valued function in a SQL Table. Firstly, let’s understand what is a table-valued function.

What is Table-Valued Function

A table-valued function is a special T-SQL code that accepts parameter/parameters and based on the conditions defined in a variable, returns the result set in the table variable. The following are the benefits of using the table-valued function:

  1.  It can be executed within the Select query.
  2. It can be used in multiple parts of a query, e.g.g in the Case statement, where/having clauses.
  3. The output of a table-valued function is a recordset, hence you can join the function with tables.

Insert Output of inline table-valued function in SQL table

In this section, I am going to explain how to insert the output of a table-valued function in a SQL table using T-SQL.

For demonstration, I am using the AdventureWorks2014 database. I  created an inline multi-valued table function named “GetEmployeesbyHireDate.” This function populates information of employees, hired within a specific date and time. The function uses the @FormDate and @Todate parameters to filter the data. The output of the function will be stored in a SQL Table.

The following code creates a function:

CREATE FUNCTION GETEMPLOYEESBYHIREDATE (@FROMDATE AS DATETIME, 
                                        @TODATE   AS DATETIME) 
RETURNS @EMPLOYEES TABLE ( 
  EMPLOYEENAME VARCHAR (MAX), 
  BIRTHDATE    DATETIME, 
  JOBTITLE     VARCHAR(150), 
  EMAILID      VARCHAR(100), 
  PHONENUMBER  VARCHAR(20), 
  HIREDATE     DATETIME ) 
AS 
  BEGIN 
      INSERT INTO @EMPLOYEES 
      SELECT ( ISNULL( B.FIRSTNAME, '') + ' ' 
               + ISNULL( B.MIDDLENAME, '') + ' ' 
               + ISNULL( B.LASTNAME, '') )AS EMPLOYEENAME, 
             A.BIRTHDATE, 
             B.JOBTITLE, 
             B.EMAILADDRESS, 
             B.PHONENUMBER, 
             A.HIREDATE 
      FROM   [HUMANRESOURCES].[EMPLOYEE] A 
             INNER JOIN [HUMANRESOURCES].[VEMPLOYEE] B 
                     ON A.BUSINESSENTITYID = B.BUSINESSENTITYID 
      WHERE  A.HIREDATE BETWEEN @FROMDATE AND @TODATE 

      RETURN 
  END

Using the Select query, we can get the output of a SQL Function. For example, you want to populate a list of employees, recruited within the year 2009. Execute the following query to get the list:

DECLARE @FROMDT DATETIME 
DECLARE @TODT DATETIME 

SET @FROMDT='2009-01-01' 
SET @TODT='2009-12-31' 

SELECT * 
FROM   GETEMPLOYEESBYHIREDATE(@FROMDT, @TODT)

The output of the above query looks as follows:

Now, create a table named “tblEmployee” to store the output of the “GetEmployeesbyHiredate” function. The following code creates the table named “tblEmployee”.

CREATE TABLE TBLEMPLOYEES 
  ( 
     EMPLOYEENAME VARCHAR (MAX), 
     BIRTHDATE    DATETIME, 
     JOBTITLE     VARCHAR(150), 
     EMAILID      VARCHAR(100), 
     PHONENUMBER  VARCHAR(20), 
     HIREDATE     DATETIME 
  )

As I mentioned earlier, we want to populate the information of the employees, who were hired in 2009. To do that, insert the output of the GetEmployeesbyHireDate function in the tblEmployees table. To do that, execute the following code:

DECLARE @FROMDT DATETIME 
DECLARE @TODT DATETIME 

SET @FROMDT='2009-01-01' 
SET @TODT='2009-12-31' 

INSERT INTO TBLEMPLOYEES 
SELECT EMPLOYEENAME, 
       BIRTHDATE, 
       JOBTITLE, 
       EMAILID, 
       PHONENUMBER, 
       HIREDATE 
FROM   GETEMPLOYEESBYHIREDATE(@FROMDT, @TODT)

Let’s verify that data has been inserted in the table. To do that, execute the following code:

SELECT * 
FROM   TBLEMPLOYEES

The output looks as follows:

Insert data in tables from Remote databases

Sometimes, you may want to extract data from the servers stored in a different data center. This can be done using SQL Linked server.

In this section, I will explain how to insert the output of the table valued function, created on the remote server. Now to demonstrate the scenario, the following is the setup.

 Host NameDatabase NameTable Name / Function Name
SourceSQL_VM_1AdventureWorks2014getCustomerByCountry
DestinationSQL_VM_2DemoDatabasetblCustomers

In the demo, we will perform the following tasks:

  1. On the source server (SQL_VM_1), create a table-valued function named “getCustomerByCountry” on the “AdventureWorks2014” database to populate the data.
  2. On the destination server, create a linked server named “Remote_Server” to execute the function (getCustomerByCountry).
  3. On the destination server, create a table, named “Customer” to store data, retrieved by the remote function (getCustomerByCountry).

The following image illustrates the setup.

The task to be performed on a Source server:

On the source server (SQL_VM_1), create a function named “getCustomerByCountry.” It populates details of a customer located in a specific country or region. The function uses the @CountryName parameter to filter the data. Execute the following code to create the function.

Alter FUNCTION Getcustomerbycountry(@CountryName VARCHAR)
returns @Customers TABLE (
  customer_name VARCHAR(500),
  phoennumber   VARCHAR(50),
  emailaddress  VARCHAR(100),
  address       VARCHAR(max),
  city          VARCHAR(150),
  country       VARCHAR(250),
  postalcode    VARCHAR(50))
AS
  BEGIN
      INSERT INTO @Customers
      SELECT customer_name,
             phoennumber,
             emailaddress,
             address,
             city,
             country,
             postalcode
      FROM   customers
      WHERE  country =@CountryName

      RETURN
  END

The tasks to be performed on Destination Server:

To populate data from the source server (SQL_VM_1), first, create a linked server between the source (SQL_VM_1) and destination (SQL_VM_2). Execute the following code on the destination server (SQL_VM_2) to create a linked server.

USE [MASTER]
GO
EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'SQL_VM_1', @SRVPRODUCT=N'SQL SERVER'
GO
EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N' Remote_Server',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'SA',@RMTPASSWORD='########'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'COLLATION COMPATIBLE', @OPTVALUE=N'TRUE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'DATA ACCESS', @OPTVALUE=N'TRUE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'DIST', @OPTVALUE=N'FALSE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'PUB', @OPTVALUE=N'FALSE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'RPC', @OPTVALUE=N'TRUE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'RPC OUT', @OPTVALUE=N'TRUE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'SUB', @OPTVALUE=N'FALSE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'CONNECT TIMEOUT', @OPTVALUE=N'0'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'COLLATION NAME', @OPTVALUE=NULL
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'LAZY SCHEMA VALIDATION', @OPTVALUE=N'FALSE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'QUERY TIMEOUT', @OPTVALUE=N'0'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'USE REMOTE COLLATION', @OPTVALUE=N'TRUE'
GO
EXEC MASTER.DBO.SP_SERVEROPTION @SERVER=N'Remote_Server', @OPTNAME=N'REMOTE PROC TRANSACTION PROMOTION', @OPTVALUE=N'FALSE'
GO

Once the linked server is created, create a SQL table to store the information of customers, and populate it by executing the SQL function, created on the source server (SQL_VM_1).

Execute the following code to create a table.

USE DEMODATABASE
GO
CREATE TABLE CUSTOMERS
  (
     ID            INT IDENTITY(1, 1),
     CUSTOMER_NAME VARCHAR(500),
     PHONENUMBER   VARCHAR(50),
     EMAILADDRESS VARCHAR(100),
     ADDRESS       VARCHAR(MAX),
     CITY          VARCHAR(150),
     COUNTRY       VARCHAR(250),
     POSTALCODE    VARCHAR(50)
  )

Using a linked server, we can execute the table-valued function created on a remote database server. When you try to execute the function using Linked server, the following error occurs:

Msg 4122, Level 16, State 1, Line 28
Remote table-valued function calls are not allowed.

Hence to execute any function on the remote server, we need to use the OPENQUERY keyword. It is used to initialize the ad hoc distributed query using a linked server. Refer this article to understand the concept of OPENQUERY.

To use OPENQUERY, we need to enable advanced configuration parameter named “Ad Hoc Distributed Queries” on the source and destination servers. Execute the following code to enable it.

USE MASTER
GO
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTION', 1
RECONFIGURE WITH OVERRIDE
EXEC SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES', 1
RECONFIGURE WITH OVERRIDE

Now I want to populate the list of customers, located in the United Kingdom and insert them in the “Customers” table. As I mentioned, the function accepts country name to filter the records. Now, we need to execute the following script on the Destination server (SQL_VM_2) to populate the list of customers located in “United Kingdom”.

SELECT CUSTOMER_NAME, 
       PHOENNUMBER, 
       EMAILADDRESS, 
       ADDRESS, 
       CITY, 
       COUNTRY, 
       POSTALCODE 
FROM   OPENQUERY([TTI609-VM2], 
'DECLARE @COUNTRY VARCHAR(150)
SET @COUNTRY=''UNITED KINGDOM''    
SELECT * FROM [ADVENTUREWORKS2014].DBO.GETCUSTOMERBYCOUNTRY(''''+ @COUNTRY +'''')' 
)

The output looks as follows:

Now, to insert data populated by in “Customers” table, execute the following script on the destination server (SQL_VM_2).

INSERT INTO CUSTOMERS (CUSTOMER_NAME,PHONENUMBER,EMAILADDRESS,ADDRESS,CITY,COUNTRY,POSTALCODE)
SELECT CUSTOMER_NAME, 
       PHOENNUMBER, 
       EMAILADDRESS, 
       ADDRESS, 
       CITY, 
       COUNTRY, 
       POSTALCODE 
FROM   OPENQUERY([TTI609-VM2], 
'DECLARE @COUNTRY VARCHAR(150)
SET @COUNTRY=''UNITED KINGDOM''    
SELECT * FROM [ADVENTUREWORKS2014].DBO.GETCUSTOMERBYCOUNTRY(''''+ @COUNTRY +'''')' 
)

/*Output*/

(1913 rows affected)

Now let’s verify whether data has been inserted properly. To check, execute the following query on the Destination server (SQL_VM_2).

USE DEMODATABASE
GO
SELECT TOP 20 CUSTOMER_NAME, 
       PHONENUMBER, 
       EMAILADDRESS, 
       ADDRESS, 
       CITY, 
       COUNTRY, 
       POSTALCODE 
FROM   CUSTOMERS

The output looks as follows:

Summary

In this article I have covered:

  1. “Insert Into” statement and its usage.
  2. How to save the output of the table-valued function in a SQL table.
  3. How to save the output of the table-valued function to the SQL table located on remote server using Linked Server.
Nisarg Upadhyay