Hi
I have a macro which runs some SQL code to update a table on a server.
The code and SQL has run fine in the past, but I've hit a road block now, seemingly because SUBSTITUTE doesn't seem to like swapping in a really long string (~35k chars) for a short string into a STRING variable.
If anyone has a suggested work around, I'd be truly grateful.
Alternatively, if there's an easier way to upload data from an Excel sheet to an SQL table that would be even better to hear of.
So, this is my problem...
I'm writing data from the Excel file to the server table, and I store into a string variable ('SQL') a SQL code 'template' (ie a string with the SQL command set up, with a placeholder for the record values I want to upload) from a cell in my workbook which has a short replaceable text string in it '{Values}'.
I run a loop to build my list of values to upload (in a string variable called 'values').
I then use SUBSTITUTE to replace the {Values} string with the contents of the 'values' variable. Up until now, all has been good.
However, I have a situation where the length of the 'values' variable is ~ 35k chars long, and the SUBSTITUTE (and REPLACE) worksheet functions don't work (the {Values} string remains unchanged).
I've tried changing the variable to a VARIANT, and had swapped SUBSTITUTE with REPLACE, but no luck.
Here's the SQL template for reference:
/* Delete all existing records for the month... */
IF (SELECT COUNT(1)
FROM ProductionRevenue.dbo.tblAdjustments
WHERE MonthEnd = '2019-08-31') > 0
BEGIN
DELETE FROM ProductionRevenue.dbo.tblAdjustments
WHERE MonthEnd = '2019-08-31'
END
/* ...then INSERT new records from the Excel template, using VALUES created
by the macro */
INSERT INTO ProductionRevenue.dbo.tblAdjustments(
MonthEnd, RecordType, StaffCode, AssgnId, ClientCode, StaffGrade, ChargeCode, StaffProductName, ContractorType, StaffOUCode, NewOUCode, JobOUCode, ProdRevOUCode, StaffHours, StaffTargetAmt, StaffBudgetAmt, StaffAllocatedBudgetRev, StaffAllocatedProvisions, StaffAllocatedEAC, StaffAllocatedWriteOffs, StaffAllocatedNetRev
)
VALUES {Values}
Many thanks for your help in advance.
PeeJay
I have a macro which runs some SQL code to update a table on a server.
The code and SQL has run fine in the past, but I've hit a road block now, seemingly because SUBSTITUTE doesn't seem to like swapping in a really long string (~35k chars) for a short string into a STRING variable.
If anyone has a suggested work around, I'd be truly grateful.
Alternatively, if there's an easier way to upload data from an Excel sheet to an SQL table that would be even better to hear of.
So, this is my problem...
I'm writing data from the Excel file to the server table, and I store into a string variable ('SQL') a SQL code 'template' (ie a string with the SQL command set up, with a placeholder for the record values I want to upload) from a cell in my workbook which has a short replaceable text string in it '{Values}'.
I run a loop to build my list of values to upload (in a string variable called 'values').
I then use SUBSTITUTE to replace the {Values} string with the contents of the 'values' variable. Up until now, all has been good.
However, I have a situation where the length of the 'values' variable is ~ 35k chars long, and the SUBSTITUTE (and REPLACE) worksheet functions don't work (the {Values} string remains unchanged).
I've tried changing the variable to a VARIANT, and had swapped SUBSTITUTE with REPLACE, but no luck.
Here's the SQL template for reference:
/* Delete all existing records for the month... */
IF (SELECT COUNT(1)
FROM ProductionRevenue.dbo.tblAdjustments
WHERE MonthEnd = '2019-08-31') > 0
BEGIN
DELETE FROM ProductionRevenue.dbo.tblAdjustments
WHERE MonthEnd = '2019-08-31'
END
/* ...then INSERT new records from the Excel template, using VALUES created
by the macro */
INSERT INTO ProductionRevenue.dbo.tblAdjustments(
MonthEnd, RecordType, StaffCode, AssgnId, ClientCode, StaffGrade, ChargeCode, StaffProductName, ContractorType, StaffOUCode, NewOUCode, JobOUCode, ProdRevOUCode, StaffHours, StaffTargetAmt, StaffBudgetAmt, StaffAllocatedBudgetRev, StaffAllocatedProvisions, StaffAllocatedEAC, StaffAllocatedWriteOffs, StaffAllocatedNetRev
)
VALUES {Values}
Many thanks for your help in advance.
PeeJay
