How to commit inserts in a single statement

[rbrodner]

In SDAC, it appears that multiple inserts are sent as multiple statements, with each statement in its own round trip to the server, with its own GO. In ADO, batches are combined into a single statement. Is it possible to make SDAC do this as well?

Here are examples of inserts on the same table, one using TMSQuery, and one using TADODataset.

------------------------Using SDAC------------------------------

EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(1)', 'G1', 'g'
go
EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(2)', 'G2', 'gg'
go
EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(3)', 'G3', 'ggg'
go
EXEC sp_executesql N'INSERT INTO ValCodes
(VCCode, VCDesc)
VALUES
(@P1, @P2)', N'@P1 varchar(2),@P2 varchar(4)', 'G4', 'gggg'
go

------------------------Using ADO-------------------------------

EXEC sp_executesql N'INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P1,@P2);
INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P3,@P4);
INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P5,@P6);
INSERT INTO "clntBurch".."ValCodes" ("VCCode","VCDesc") VALUES (@P7,@P8)',
N'@P1 varchar(2),@P2 varchar(1),@P3 varchar(2),@P4 varchar(2),@P5 varchar(2),@P6 varchar(3),@P7 varchar(2),@P8 varchar(4)',
'H1', 'h', 'H2', 'hh', 'H3', 'hhh', 'H4', 'hhhh'

[AndreyZ]

Hello,

To obtain the same functionality in SDAC, you should set the TMSQuery.CachedUpdates property to True and the TMSQuery.Options.UpdateBatchSize property to the number of commands that will be executed in a batch.

[rbrodner]

Thanks - I looked in help but could not find the correct property. I notice that when I set this to a value > 1, my identity columns don't seem to update correctly after a commit. Is there something else I need to do to make them work correctly?

[AndreyZ]

Thank you for the information. We reproduced the problem and the investigation is in progress. We will notify you when we have any results.

[AndreyZ]

We have investigated this problem. Unfortunately, adding of such functionality is a very hard task. We will investigate the possibility of implementing this functionality but we cannot provide any time frame. ADO works when there are IDENTITY fields only because it stops to use batches and inserts records using multiple statements. You can achieve the same behaviour with SDAC by setting the TMSQuery.Options.UpdateBatchSize property to 1.