Here is what I ended with.
Assuming that data is read from the table using the following:
select * from someTable where st_id between 100 and 200
for xml path, root('rows')
I get this data from one server and store it as a tuple (schema, table, keys, xml), where:
schema
is schema name (e.g. dbo
)
table
is table name (e.g. someTable
)
keys
is comma-separated list of "keys" (columns used for <match>
in merge ... on <match>
, e.g. st_id
in example above, if there are more keys they should be passed,like,this
)
xml
is data as an xml (easy)
This data is then passed to some other server, where there is one function and one procedure defined.
Function tools.GenerateXMLMergerForTable
creates body (whole declaration in fact) of procedure used for merging data read from xml
into table table
in schema schema
using parsed string keys
as <match>
.
Procedure tools.createXMLMergerForTable
executes aforementioned function and creates procedure that takes single parameter (of type XML) and merges data read from it into table that it was configured for.
T-SQL follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION tools.GenerateXMLMergerForTable
(
@tableschema nvarchar(max), @tablename nvarchar(max), @matchcols nvarchar(max), @procname nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
declare @str nvarchar(max) = '
create or alter procedure tools.sp_AutoMergerForTableXXXPROCEDURENAMEXXX @x xml
as
begin
begin try
DECLARE @handle INT, @PrepareXmlStatus INT
EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @x
merge XXXTABLENAMEXXX as tgt
using (SELECT * FROM OPENXML(@handle, ''/rows/row'', 2) WITH XXXTABLENAMEXXX)
as src (XXXFULLCOLUMNLISTXXX)
on XXXMATCHLISTXXX
when matched
then update set
XXXSETLISTXXX
when not matched by target
then
insert (XXXFULLCOLUMNLISTXXX)
values (XXXFULLCOLUMNLISTXXX);
EXEC sp_xml_removedocument @handle
end try
begin catch
end catch
end
'
declare @collist nvarchar(max) = '', @setlist nvarchar(max) = '', @matchlist nvarchar(max) = '', @fullcollist nvarchar(max) = ''
select @collist = @collist + quotename(COLUMN_NAME) + ','
, @setlist = @setlist + quotename(column_name) + ' = src.' + quotename(column_name) + ', '
from INFORMATION_SCHEMA.COLUMNS where
TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename and COLUMN_NAME not in (select * from string_split(@matchcols, ','))
order by ordinal_position
select @fullcollist = @fullcollist + quotename(COLUMN_NAME) + ','
from INFORMATION_SCHEMA.COLUMNS where
TABLE_SCHEMA = @tableschema and TABLE_NAME = @tablename
order by ordinal_position
select @matchlist = @matchlist + 'tgt.' + QUOTENAME(trim(value)) + '=src.' + QUOTENAME(trim(value)) from string_split(@matchcols, ',')
declare @result nvarchar(max) = replace(@str, 'XXXCOLUMNLISTXXX', trim(', ' from @collist))
set @result = REPLACE(@result, 'XXXSETLISTXXX', trim(', ' from @setlist))
set @result = REPLACE(@result, 'XXXTABLENAMEXXX', @tablename)
set @result = REPLACE(@result, 'XXXMATCHLISTXXX', REPLACE(trim(@matchlist), ' ', ' and '))
set @result = REPLACE(@result, 'XXXPROCEDURENAMEXXX', @procname)
set @result = REPLACE(@result, 'XXXFULLCOLUMNLISTXXX', trim(', ' from @fullcollist))
RETURN @result
END
GO
create or alter procedure tools.createXMLMergerForTable @schema nvarchar(max), @table nvarchar(max), @matchcols nvarchar(max), @procsuffix nvarchar(max)
as
begin
declare @body nvarchar(max) = tools.GenerateXMLMergerForTable(@schema, @table, @matchcols, @procsuffix)
exec sp_executesql @body
end
go
Java code simply reads tuple defined above, inserts parameters that it knows off, creates random suffix, creates merging procedure, executes it using xml
from The Tuple and drops previously created procedure.
It seems to work as advertised, though no guarantees are provided.
NOTE: it is assumed that function, procedure and subsequently created procedures live in schema tools
.
NOTE 2: It is possible that you will get information about string_split
object not found, if so then just do:
ALTER DATABASE <your database name>
SET COMPATIBILITY_LEVEL = 130;
For reasons see documentation of string_split and how to change compatibility level.
with ... merge ... when matched then update set <a lot of columns>
. Now that you saidit's unclear how XML would help
I see that main problem is still there - I won't be able to avoid specifying columns names inupdate
orinsert
sub-clauses inmerge
. It seems that dynamic SQL is the only solution - or maybe now, maybe I could automatically generate procedure for each table. Thanks for the comment.