How to pass a list of parameter to stored procedure and perform batch insert in SQL Server

Question

I'm using Spring JDBCTemplate to connect to the SQL Server.

I have a list of Objects that needed to be inserted into a table of SQL Server.

What I did is the following:

public void batchInsert(final List<Bean> list) {

    final String sql = "insert into temp"
            + "(id, name, amount, location, time, price) "
            + " values (?, ?, ?, ?, ?, ?)";

    getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Bean vo = list.get(i);
            ps.setString(1, vo.getId());
            ps.setString(2, vo.getName());
            ps.setDouble(3, vo.getAmount());
            ps.setString(4, vo.getLocation());
            ps.setString(5, vo.getTime());
            ps.setDouble(6, vo.getPrice());
        }

        @Override
        public int getBatchSize() {
            return list.size();
        }
    });
}

But now, I'd like to pass the parameter List<Bean> list to a stored procedure which handle the batch insert as high efficient as possible.

May I ask how to implement this?

Depending on your version of SQL Server you may want to look at [Table valued parameters](http://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx) — GarethD, Feb 21 '14 at 07:55
Nevermind, it looks like JDBC [doesn't support them yet](http://blogs.msdn.com/b/jdbcteam/archive/2012/04/03/how-would-you-use-table-valued-parameters-tvp.aspx), when they do though this will be the way forward. [This thread](http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7377f1c-f235-4870-b4a9-eab041fbd7b5/is-tablevalued-parameters-available-in-java-jdbc?forum=sqldatabaseengine) suggests a work around, but is not very clear about it, and I don't know Java well enough to help I am afraid. — GarethD, Feb 21 '14 at 08:00

score 1 · Answer 1 · answered Mar 05 '14 at 05:44

1

First of all don't write query directly as it can be security breach to Your application. For bulk data storage the way normally get followed is XML Way. Try to implement it through XML.

answered Mar 05 '14 at 05:44

Mukesh Kumar

656
5
26

score 1 · Answer 2 · edited May 23 '17 at 12:33

You can use Table variable parameter for your stored procedure :

CREATE TYPE RecordList AS TABLE (Id Int, Name NVarChar(100), Amount int, location nvarchar(100), ...)

CREATE PROCEDURE test 
    @RecordList dbo.RecordList READONLY
AS Begin
    Insert Into temp (id, name, amount, location , ...)
    Select id, name, amount, location, ...)
    from @RecordList
End

I don't work with Spring JDBCTemplate but you can use following reference in order to use table variable parameter in C#:

How to pass table value parameters to stored procedure from .net code

C# and Table Value Parameters

score 0 · Answer 3 · answered Feb 21 '14 at 08:38

0

If you can't use Table-Valued parameters, why not xml parameter in your Stored Procedure?

answered Feb 21 '14 at 08:38

Antonio

535
10
26

1

Would you please a little bit more – macemers Feb 27 '14 at 02:18

score 0 · Answer 4 · answered Feb 27 '14 at 08:34

Pasre XML to dataset:

DECLARE @ParseXML xml

--SET @ParseXML  = ?        Your xml, don't use UTF-8
SET  @ParseXML = '
<row>
  <id>1</id>
  <name>Alex</name>
  <amount>3</amount>
  <location>GBR</location>
  <time>2014-02-27T08:25:19.113</time>
  <price>5.25</price>
</row>
<row>
  <id>2</id>
  <name>John</name>
  <amount>1</amount>
  <location>USA</location>
  <time>2014-02-27T08:25:19.113</time>
  <price>3.00</price>
</row>
<row>
  <id>3</id>
  <name>Sam</name>
  <amount>4</amount>
  <location>CAN</location>
  <time>2014-02-27T08:25:19.113</time>
  <price>7.55</price>
</row>
';

--INSERT INTO Temp
SELECT
    T.c.value('./id[1]','int') as id
    ,T.c.value('./name[1]','nvarchar(255)') as name
    ,T.c.value('./amount[1]','int') as amount
    ,T.c.value('./location[1]','nvarchar(255)') as location
    ,T.c.value('./time[1]','datetime') as [time]
    ,T.c.value('./price[1]','money') as [time]
FROM @ParseXML.nodes('//row') T(c);

@user838204 MS SQL 2005+ http://technet.microsoft.com/en-us/library/ms188282(v=sql.90).aspx — AlexK, Feb 27 '14 at 11:19

score 0 · Answer 5 · answered Mar 03 '14 at 13:41

XML Stored procedures are available since SQL 2005

basically, you declare an input parameter of xml type in your procedure declaration.

then, in your procedure, you could use different methods to parse xml. Maybe the easiest is to use the OPENXML method and sp_xml_preparedocument/sp_xml_removedocument

Suppose your xml variable is something along this:

<Order>
    <OrderID>12345</OrderID>
    <CustomerID>ABCD-1234</CustomerID>
    <OrderDate>20140101</OrderDate>
</Order>

Procedure should be something like :

CREATE PROCEDURE MyProc @xmlData xml
AS

EXEC sp_xml_preparedocument @i OUTPUT, @xmlData

select *
from
  openxml (@i, '/ROOT/Order', 1)
  with
  ( OrderID        int
  , CustomerID     nchar(5)
  , OrderDate      datetime)
  INTO MyOrders

EXEC sp_xml_removedocument @i

see: http://technet.microsoft.com/it-it/library/ms186918.aspx on how to use OPENXML

score 0 · Answer 6 · edited May 23 '17 at 11:52

While a table-valued parameter or constructing XML is the modern approach, if that's not available to you or you don't want that overhead, you could simply concatenate the many INSERT statements and send them in as a single batch to SQL Server. If you need to do some processing as part of the insertion, you the batch could use a temporary table which would be accessible to a stored procedure called at the end of the batch.

How to pass a list of parameter to stored procedure and perform batch insert in SQL Server

6 Answers6

Linked