Introduction to Inserting Multiple Columns from Multiple Parameters in SQL

As a technical blogger, I’ve encountered numerous questions and scenarios where users need to insert data into multiple columns based on various input parameters. In this article, we’ll explore two common approaches to achieve this: using the STRING_SPLIT function (introduced in SQL Server 2016) and table-valued parameters.

Understanding Table-Valued Parameters

Table-valued parameters (TVPs) are a feature introduced in SQL Server 2008. They allow you to pass a table as an input parameter to stored procedures, making it easier to work with complex data sets.

To use TVPs, you’ll need to create a user-defined type (UDT) that defines the structure of your table-valued parameter. This UDT will be used to declare the parameters in your stored procedure.

Creating a Table-Valued Parameter

Let’s create an example UDT for our scenario:

CREATE TYPE SplitTableType AS TABLE (
    Value varchar(20)
);

This UDT SplitTableType defines a table with one column, Value, which will store the individual values from the input parameter.

Declaring Table-Valued Parameters

Now that we have our UDT, we can declare table-valued parameters in our stored procedure:

CREATE PROCEDURE InsertDataFromTVP
    @RecepientID SplitTableType READONLY,
    @LocationID  SplitTableType READONLY,
    @BranchID    SplitTableType READONLY
AS
BEGIN
    -- Insert data into your table
    INSERT INTO YourTable (RecipientID, LocationID, BranchID)
    SELECT s1.Value AS Recipient, s2.Value AS Location, s3.Value AS Branch
    FROM @RecepientID s1,
         @LocationID s2,
         @BranchID s3;
END;

In this example, our stored procedure InsertDataFromTVP accepts three table-valued parameters: @RecepientID, @LocationID, and @BranchID. These parameters are of type SplitTableType.

Using STRING_SPLIT

As mentioned earlier, SQL Server 2016 introduced the STRING_SPLIT function, which allows you to split a string into individual values based on a specified separator.

To use STRING_SPLIT, you’ll need to create a table-valued parameter (in this case, a scalar value) that contains the input string. Then, you can pass this table-valued parameter as an argument to your stored procedure.

Creating a Table-Valued Parameter for STRING_SPLIT

Let’s modify our UDT SplitTableType to include a scalar value:

CREATE TYPE SplitTableType AS TABLE (
    Value varchar(20)
),
VALUES (@RecepientID);

In this modified version, we’ve added the new values to our UDT. We’re also passing the scalar value @RecepientID directly as part of our table-valued parameter.

Using STRING_SPLIT

Now that we have our table-valued parameter ready, let’s use it with STRING_SPLIT:

CREATE PROCEDURE InsertDataFromTVP
    @SplitValues TABLE (Value varchar(20)) READONLY
AS
BEGIN
    -- Split the string into individual values
    INSERT INTO YourTable (RecipientID, LocationID, BranchID)
    SELECT s1.Value AS Recipient, s2.Value AS Location, s3.Value AS Branch
    FROM @SplitValues s1,
         STRING_SPLIT(s1.Value, '|') s2 ,
         STRING_SPLIT(s1.Value, '|') s3;
END;

In this example, our stored procedure InsertDataFromTVP accepts a table-valued parameter of type TABLE (Value varchar(20)). This parameter is used as the source for the STRING_SPLIT function.

Example Usage

Let’s use both approaches to demonstrate how to insert data into multiple columns based on input parameters:

Using Table-Valued Parameters

-- Create a sample table
CREATE TABLE YourTable (RecipientID varchar(20), LocationID varchar(20), BranchID varchar(20));

-- Insert some sample data
INSERT INTO YourTable (RecipientID, LocationID, BranchID)
VALUES ('1|2', '7|8|9|10', '1|5|9');

-- Call the stored procedure with table-valued parameters
EXEC InsertDataFromTVP @RecepientID = SplitTableType(
    VALUES ('1'),
    ( '2' )
), 
@LocationID  = SplitTableType(
    VALUES ('7'),
    ( '8' ),
    ( '9' ),
    ( '10' )
), 
@BranchID    = SplitTableType(
    VALUES ('1'),
    ( '5' ),
    ( '9' )
);

Using STRING_SPLIT

-- Create a sample table
CREATE TABLE YourTable (RecipientID varchar(20), LocationID varchar(20), BranchID varchar(20));

-- Insert some sample data
INSERT INTO YourTable (RecipientID, LocationID, BranchID)
VALUES ('1|2', '7|8|9|10', '1|5|9');

-- Call the stored procedure with a table-valued parameter for STRING_SPLIT
EXEC InsertDataFromTVP @SplitValues = TABLE (
    ( '1' ),
    ( '2' )
),
( '7|8|9|10' ),
( '1|5|9' );

Choosing the Right Approach

When deciding between using table-valued parameters and STRING_SPLIT, consider the following factors:

• Performance: If you need to insert a large number of rows, using table-valued parameters might be more efficient since it avoids the overhead of parsing strings.
• Complexity: Using STRING_SPLIT is often easier when dealing with complex string patterns or concatenating multiple values. However, it can become cumbersome when working with many values.
• Readability: TVPs tend to make your codebase cleaner and more readable due to their explicit nature.

Ultimately, the choice between these approaches depends on your specific requirements, performance needs, and personal preference.

Conclusion

Inserting data into multiple columns based on input parameters is a common scenario in database development. By leveraging table-valued parameters and STRING_SPLIT, you can effectively handle complex data sets and improve the efficiency of your stored procedures.

Last modified on 2024-10-01