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

Question

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?

Table valued parameters? msdn.microsoft.com/en-us/library/bb510489.aspx — Kritner
– Kritner, Commented Apr 19, 2016 at 15:20
@Kritner: But how would one call this from Access? -- If a stored procedure is a must, I would probably join them together and pass as one delimited string. Then use a Split function of your choice to extract them. But actually I'd rather insert the rows directly from Access. — Andre
– Andre, Commented Apr 19, 2016 at 16:17
@Andre How would I insert each split value as a new record in SQL Server? I am thinking of maybe doing something like this in Access For Each....value in array execute stored procedure next Although I feel there is a much better way to do this. — Adib Akale
– Adib Akale, Commented Apr 19, 2016 at 16:51
I meant, I wouldn't have this as part of a stored procedure, thus no joining or splitting. Somewhere you collect these CC numbers, and from there I would directly insert them into the table (execute INSERT statement(s) or with DAO). — Andre
– Andre, Commented Apr 19, 2016 at 18:13
@AdibAkale if your question has been answered, please accept the answer. If not, please let use know what you are still missing :) — C. White
– C. White, Commented May 17, 2016 at 18:02

C. White · Accepted Answer · 2016-04-22 01:50:29Z

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!

Y.B. · Accepted Answer · 2016-04-20 16:42:18Z

0

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.

answered Apr 20, 2016 at 16:42

Y.B.

3,58617 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related