DynamicSQL services to do an batch insert

, ,
1

Hi Experts,

Could we use DynamicSQL services to do an batch insert ? If yes could i get a sample for the same ?

Thank you.

Regards,
Nis

2

Hi Nischal,

there is a BatchInsert-Template available in the JDBC-Adapter.

If this is not applicable for you, please describe your requirement in detail.

Can you provide version details for IS and JDBC-Adapter please?

Regards,
Holger

3

Nope, any reason for not using Batch Insert adapter service?

4

Yes, You can use but it doesn’t make sense as already built-in adapter service exist to meet the requirement.

Thanks,

5

Abdul,

Sounds interesting :slight_smile: I never know we can use dynamic sql as a batch insert.

Can you please explain how?

6

This is something which you have manually write to obtain the functionality. Below is the pseudo code:

DECLARE @sSourceType AS VARCHAR(10)
DECLARE @sPath AS VARCHAR(50)
DECLARE @sFileName AS VARCHAR(50)
DECLARE @sSourceTableName AS VARCHAR(50)
DECLARE @sSourceFMTFile AS VARCHAR(50)
DECLARE @iSourceID AS INT
DECLARE @SQL AS VARCHAR(500)

SET @iSourceID = (SELECT TOP 1 iID FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sPath = (SELECT TOP 1 sPath FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sFileName = (SELECT TOP 1 sFileName FROM SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sSourceTableName = (SELECT TOP 1 sSourceTableName FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))
SET @sSourceFMTFile = (SELECT TOP 1 sSourceFMTFile FROM dbo.SX_SourceFiles WHERE iID NOT IN (SELECT iSourceID FROM SX_ImportHold))

PRINT @sSourceTableName

EXEC (‘Truncate table ’ + @sSourceTableName )
PRINT @sSourceTableName + ’ has been truncated’

SET @SQL =
(‘BULK INSERT ’ + @sSourceTableName + ’
FROM ‘’’ + @sPath + @sFileName + ‘’’
WITH (FORMATFILE = ‘’’ + @sSourceFMTFile + ‘’‘)’)

Even you can find lots of examples on this by taking google’s help.

Thanks,

7

I did see this script on internet but for me it looks easy to go with Batch Insert adapter service. Thanks for your time.

8

My intention is to say we can achieve the same with dynamic SQL, that’s all. I use dynamic SQL in my project where it generates field dynamically which I use it as unique field. By the way where are you working now ?

Thanks,