I have a table with two different versions. The latest release contains one extra column. However, I need to do an insert statement like the following depends on the column counts but sql will throws an error message
"Msg 213, Level 16, State 1, Line 6 Column name or number of supplied values does not match table definition."
IF (SELECT COUNT(*)FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name LIKE '%Test3%') = 2 begin insert into [Test_DB].[dbo].[Test3] (Col, Col1) values (1,'col1'); end else begin insert into [Test_DB].[dbo].[Test3] (Col, Col1, Col2) values (2,'col1','col2'); end
Does anyone knows a good way to do this without using dynamic sql statement?
asked Jul 29, 2014 at 08:30 PM in Default
The problem is MSSQL is not going to process your second INSERT statement (the one in the ELSE) because the column [Col2] does not exist at the point of compilation. It is therefore unable to create an execution plan.
What's interesting to me is, I was only able to reproduce the problem by executing a test script in two stages. The test script works fine if executing on a single batch, which is interesting....
So, the problem only happens if the table already exists in your schema, and the above explanation holds true. MSSQL can not compile and create an execution plan coz Col2 does not exist.
Test script follows:
To solve the problem, even if executing in two stages.... Hmmm, I guess you'd need two different versions of the PROC (one with 2 columns and one with 3 on the updated database). You can do that in order not to resort to dynamic SQL.
Another option, you could use a temp table on the insert statements (i.e. work with a copy). That would provide the desired flexibility. Problem is, you'd need to drop and recreate the original table with the copy every time.... Not practical...
answered Jul 29, 2014 at 09:43 PM
Thanks for your input. Actually, I am able to work around using #temp table in the insert statement. Actually, sql doesn't complain when I run the following. However, if I click to do the estimate execution plan, it still throws an Msg 207, Level 16 error.
IF OBJECT_ID(N'tempdb..#Testtbl', N'U') IS NOT NULL BEGIN DROP TABLE #Testtbl end GO create table #Testtbl(id int,col1 nvarchar(10),col2 nvarchar(10)); insert into #Testtbl values (1,'test1','') insert into #Testtbl values (2,'test2','test2a')
IF (SELECT COUNT(*)FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = 'TestTable') = 1 begin insert into [Test_DB].[dbo].[TestTable](col1) ( select Col1 from #Testtbl where ID =1 ) end else begin insert into [Test_DB].[dbo].[TestTable] (col1,col2) ( select col1, col2 from #Testtbl where ID =2 ) end
drop table #Testtbl
answered Jul 30, 2014 at 04:03 PM