CODEWITHSUNDEEP
arrayssql-servert-sqlms-accesssql-insert
Adib Akale
Adib Akale

Reputation: 105

Insert multiple values into multiple rows using one parameter in T-SQL

I am building an application using Microsoft Access as a front-end and SQL Server as the back end.

I have a Stored Procedure that inserts customer information. One of the pieces of information that will be inserted for each customer are their Credit Card numbers. The number of credit cards for each customer could be anywhere from 1-50.

I am planning on using a Dynamic array in VBA to add the credit card numbers. The trouble I am having is I am not quite sure how to pass the values to the stored procedure.

My first thought was to create a ton of parameters (CC Number 1, CC Number 2, CC Number 3,etc) but obviously this isn't the correct way to do it.

After the credit card numbers have been added to the Dynamic Array, I can ReDim it to get the count of how many credit card numbers I will need to insert.

What would be the best way to pass lets say, 14 credit card values to one parameter and insert each value as a new row?

Upvotes: 2

Views: 1359

Answers (2)

C. White
C. White

Reputation: 800

Unfortunately, AFAIK, VBA doesn't support table valued parameters (ADO.NET does, but not the vanilla VB6 implementation that we have to use in Access).

You could send a stream of function calls to the server by looping through a recordset or something, but that's going to be painfully slow. If you don't mind repeating yourself, though, you could send one big command that contains a bunch of EXEC statements chained one after the other (if you're using DAO, be sure to specify SET NOCOUNT ON at the start of the query).

If you're looking for a pure-SQL solution, here's the strategy I take for these sorts of problems:

  • Concatenate the array of values into a string with some sort of separator (e.g. "," or "|").
  • Pass the string to a function or query that converts it to a table of values.
  • INSERT or MERGE the table of values into the final target table.

Here's an example of how you can do it:

SET NOCOUNT ON

DECLARE @x XML;
DECLARE @CreditCards AS TABLE (CreditCardNumber VARCHAR(16));
DECLARE @FinalTable AS TABLE (CreditCardNumber VARCHAR(16));
DECLARE @CreditCardList AS VARCHAR(8000);
DECLARE @Divider AS Varchar(10);

SET @Divider=',';
SET @CreditCardList='1234567890123456,1111111111111111,2222222222222222,123456789012345';

IF NOT @CreditCardList IS NULL 
  BEGIN 
    SELECT @x = CAST('<A>'+ REPLACE(@CreditCardList,@Divider,'</A><A>')+ '</A>' AS XML);
    INSERT INTO 
      @CreditCards            
    SELECT 
      t.value('.', 'varchar(16)') AS inVal
    FROM 
      @x.nodes('/A') AS x(t) ;
  END 

INSERT INTO 
  @FinalTable
SELECT 
  CreditCardNumber
FROM
  @CreditCards 

SELECT * FROM @FinaLTable

XML isn't the fastest way to do the conversion, but it has the benefit of being relatively straightforward. Jeff Moden provides several pretty inspired approaches to the problem in his blog post Tally OH! An Improved SQL 8K “CSV Splitter” Function.

Hope that helps!

Upvotes: 1

Y.B.
Y.B.

Reputation: 3586

Use XML to pass all customer information in one document. This way you can pass any number of parameters and query it on server as a table.

Upvotes: 0

Related Questions