| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| E-sql Row-Wise Binding - PASSTRU - SQLSERVER ODBC |
« View previous topic :: View next topic » |
| Author |
Message
|
| kintan |
 Posted: Sun Nov 20, 2016 4:30 pm Post subject: E-sql Row-Wise Binding - PASSTRU - SQLSERVER ODBC |
 |
|
Newbie
Joined: 20 Nov 2016
Posts: 1
|
Integraton Bus 9.0.0.3
ODBC SQL Server cli 6.01.7601.17514.
Hi, i could do an insert of varios sentences with passthru(esql-MB) using:
| Code: |
INSERT INTO db.Table (Col1, Col2,Col3) VALUES
( ?, ?, ?), ( ?, ?, ?), ( ?, ?, ?) |
and binding an array of values against DB2 Database.
But im not able to do the same against sql server database; i think also that DataInsert node is putting one by one the rows to database.
Some guidines to make inserts against sqlserver odbc in batch of rows in order to fix performance problems?
Thanks. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Sun Nov 20, 2016 10:48 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Please use [c o d e] tags in your posts. They make reading them a lot easier. for example...
Integraton Bus 9.0.0.3
ODBC SQL Server cli 6.01.7601.17514.
Hi, i could do an insert of varios sentences with passthru(esql-MB) using:
| Code: |
INSERT INTO db.Table (Col1, Col2,Col3) VALUES
( ?, ?, ?), ( ?, ?, ?), ( ?, ?, ?)
|
and binding an array of values against DB2 Database.
But im not able to do the same against sql server database; i think also that DataInsert node is putting one by one the rows to database.
Some guidines to make inserts against sqlserver odbc in batch of rows in order to fix performance problems?
Thanks.
-------------
Have you tried that bit of SQL against SQLServer using the Management Studio?
Whenever I run into issues like this I verify that the SQL actually works.
There are differences in the different DB implementations of SQL. Some are closer to the 'standard' than others.
If it is invalid in the SQL Management studio, you will have to change it to multiple Passthru's.
Just a point, are any of the data types TIMESTAMP? This is one area where SQLServer and Oracle work a bit differently. I can't recall if DB2 is also different.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| maurito |
| Posted: Mon Nov 21, 2016 4:22 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| You can use the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. For more information, see Table Value Constructor (Transact-SQL). |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Nov 21, 2016 4:37 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| maurito wrote: |
| You can use the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. For more information, see Table Value Constructor (Transact-SQL). |
This will have to be done in an ESQL passthru statement, and you will have to do things in regular ESQL to give the passthru statement the right kind of inputs.
There is discussion in the IIB documentation on how IIB data structures map to database data structures.
You may find it easier/better to use a stored procedure as the interface between IIB and your database.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
