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

Advanced SQL: Insert output of the parameterized table-valued function in SQL table
3 (60%) 3 votes

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:

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:

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:

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

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.

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:

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

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:

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:

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

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:

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

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:

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:

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

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:

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

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.

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.

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.

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:

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.

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

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

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

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

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