SQL Server : insert into with openxml and timestamp

Question

Ok, here is the process that was working

1. xml files in our production server xml files where being created for each table

2. based of those xml files the data was imported

One thing has changes in production. A field called RowVer was created that is a timestamp.

So now on the xml files that are exported the RowVer and its value is there. Not only that its encoded in base64, and there is now way to insert explicit values into a timestamp field.

Anyway, in the stored procedure, there is a rules table we have were if the table is x and the field is y then ignore it.

Basically the SQL code is this:

--declare @ImportPath varchar(1000) = 'E:\ExportTest\171026140442\' --bunch of xml files
--declare @DB varchar(1000) = 'DestinationDb.dbo'
--declare @debug bit = 1
ALTER PROCEDURE [dbo].[STP_StageImp_ImportData]
    @ImportPath varchar(1000),
    @DB varchar(1000),
    @debug bit
AS

if object_id('tempdb..#Files') is not null drop table #Files
create table #Files
(
ID int
,TableName varchar(1000)
,Depth int
,IsFile bit
)

insert into #Files (TableName, Depth, IsFile) exec xp_dirtree @ImportPath, 10, 1

declare @DataTableName varchar(1000)
declare @TargetDataTableName varchar(1000)
declare @sql nvarchar(max)


while (select count(*) from #Files) > 0
 begin
    set @DataTableName = (select top 1 TableName from #Files order by ID, TableName)



if object_id('tempdb..#RawData') is not null drop table #RawData

create table #RawData
(
TableName varchar(1000),
RawXML xml
)

set @sql = ' insert into #RawData
            select ''' + parsename(@DataTableName,2) + ''', BulkColumn from openrowset(  
            bulk ''' + @ImportPath + @DataTableName + ''',  
            single_nclob) as x'


exec (@sql)

declare @XMLData xml
select @XMLData = RawXML from #RawData

declare @ColumnNames nvarchar(MAX)
declare @ColumnSchema nvarchar(MAX)
declare @hDoc int

if object_id('tempdb..#columns') is not null drop table #columns
create table #columns
(
    column_name varchar(1000),
    column_full varchar(1000)
)


insert into #columns
exec STP_StageImp_TableCols @TargetDataTableName


delete from #columns where column_name IN ('ID','RowVer')

    delete @data
insert into @data select c.Column_Name + ',' from #columns c
exec STP_StageImp_MergeRows @data, @debug, @ColumnNames output


exec sp_xml_preparedocument @hDoc output, @XMLData

set @sql = N'insert into ' +  @TargetDataTableName + ' select * from openxml(@hDoc, ''table/row'') with (' + @ColumnSchema + N')'

exec sp_executesql @SQL, N'@hDoc int', @hDoc

exec sp_xml_removedocument @hDoc

delete from #Files where TableName = @DataTableName
end

The error is

server column name or number of supplied values does not match table definition

BUT THAT IS SIMPLY NOT TRUE

If I run select * from Destionation.dbo.Table and dumped into an Excel file and the table schema and did a comparison

select * from openxml(@hDoc, ''table/row'') with (' + @ColumnSchema + N')'

I verified the destination tables does not have any "hidden" fields.

if i change it to

set @sql = N'insert into ' +  @TargetDataTableName + ' (' + @ColumnNames + ')
             select * from openxml(@hDoc, ''table/row'') with (' + @ColumnSchema + N')'

(the difference above is using the @ColumnNames explicitly) THIS WORKS

So in the end... I did not want to have to change our flash stage to QA stored procedures, because of a new timestamp column.

If anyone has any idea why adding a timestamp column to a table would break insert into table select * fro openxml, I would appreciate it.

This is not super high priority, as I have a workaround, but if anyone has any suggestions post away.

Answer 1

The error is because your insert is not explicitly naming the columns. The exact warning I posted in a comment a couple hours ago. This is very easy to prove.

create table Something
(
    SomeVal int
)

insert Something
select 1

select * from Something

--at this point you have 1 row in the table

--now add the new column
alter table Something add asdf timestamp

insert Something
select 1
--whoa....it failed.

--but if I name the columns it works again.
insert Something(SomeVal)
select 1

select * from Something

You might also take a look at the documentation as the timestamp datatype has been deprecated for quite some time. https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql