MULTIPLE INSERTS IN A SINGLE SQL Query template

[Former Member]

HI Experts,

I want to insert multiple records in a single SQL statement in a MII query template

I tried using

INSERT INTO TABLE (ID, Value)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third')

But its not working out for me

Writing multiple insert statement is also an option but i want to write single insert statement and values separated by comma

Could any one kindly tell me how to insert multiple records in a single line with values separted by a  comma

Thanks

Praveen

[swaroop_anasane]

Hi Praveen,

Below syntax should help you:

insert

into Table

values

('XYZ1','Tenth',25,'Rowdy Rathore'),('XYZ1','Eleventh',29,'Deff Archy')

Table here has four columns excluding ID column. Should work perfectly fine. You can mould your dataset in above format and pass.

Thanks,

Swaroop

[Former Member]

Hi Praveen

If your use case is like having large no. of data rows and inserting those into DB. I believe best appropriate way would be form a xml and then pass it to DB. While in DB, you can create SP and perform your logical steps(if any) thereafter inserting data into table.

The above link shared by Muzammil talks on the same subject.

While once within SP(DB layer), you can fetch entire xml using below example:

DECLARE @data XML;

-- Element-centered XML

SET @data = '<data>

    <customer>

      <id>1</id>

      <name>Name 1 </name>

    </customer>

    <customer>

      <id>2</id>

      <name>Name 2</name>

    </customer>

     <customer>

      <id>3</id>

      <name>Name 3</name>

    </customer>  

    </data>';

SELECT T.customer.value('(id)[1]', 'INT') AS customer_id,

       T.customer.value('(name)[1]', 'VARCHAR(20)') AS customer_name

FROM @data.nodes('data/customer') AS T(customer);

The above run will give you output from xml in single shot.

You can also find maximum no. of rows as below.

declare @max int

select @max = @data.value('fn:count(/data/customer/id)','int')

select @max  

I believe above should help you around with your insertion...

[Former Member]

Hi Praveen,

For multiple row insert, you are try checking this link

Muzammil

[Former Member]

Hi Praveen,

Two things to take care of.

1. Select Mode in Query Template as "Command"

2. Maintain the exact format to insert.

example-1 for testing with sample records or fixed data

Insert into table_name (Col1, Col2, Col3,...)

values ('Val2', 'Val2','Val3'....)

Example - 2 for real time with dynamic data. Maintain Param details in Parameter tab

Insert into table_name (Col1, Col2, Col3,...)

values ([Param.1], [Param.2],]Param.3],....)

In case if you want to insert multiple records, use BLS

Regards

Muzammil