DynamicSQL services to do an batch insert

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

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

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

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

Thanks,

Abdul,

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

Can you please explain how?

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,

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

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,