x

how to do insert to sql table with two different versions

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?

more ▼

asked Jul 29 at 08:30 PM in Default

chung gravatar image

chung
1

I am guessing this is a hosted application and your are doing pass through sql? In other words, this code exists in your application and you want the same code to work for both versions of the database. This is one reason we like to use stored procedures. It avoids this kind of thing. I would not use dynamic sql here. What is wrong with what you posted?
Jul 29 at 08:38 PM seanlange
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

USE [D_DP_Adhoc]
GO


-- Part 1
-- Create a Test Table
------------------------------------------------------------

SELECT
    '1'    AS [Col]
  , 'Col1' AS [Col1]

  -- Uncomment to Test Initial stage 
  -- , 'Col2' AS [Col2]

INTO dbo.TestTable;



-- Part 2
-- Error only occurs if executing Part 1 and 2 separately
-- Simultaneous execution works fine
------------------------------------------------------------


IF (    SELECT COUNT(*) 
       FROM sys.columns C 
       JOIN sys.tables  t
         ON c.object_id = t.object_id 
       WHERE t.name = 'TestTable'
   ) = 2 
BEGIN
    insert into dbo.TestTable (Col, Col1) 
    values (2,'col1');
END
ELSE
BEGIN
    insert into dbo.TestTable (Col, Col1, Col2) 
    values (2,'col1','col2');
END


-- Review
SELECT * FROM TestTable;

-- Maintennance
DROP TABLE TestTable;

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...

-- Copy, and work with a copy
SELECT *
INTO #Copy
FROM TestTable;

IF (    SELECT COUNT(*) 
       FROM sys.columns C 
       JOIN sys.tables  t
         ON c.object_id = t.object_id 
       WHERE t.name = 'TestTable'
   ) = 2 
BEGIN
    insert into #Copy (Col, Col1) 
    values (2,'col1');
END
ELSE
BEGIN
    insert into #Copy (Col, Col1, Col2) 
    values (2,'col1','col2');
END


-- Review
SELECT * FROM #Copy;
more ▼

answered Jul 29 at 09:43 PM

virtualjosh gravatar image

virtualjosh
311 1 2

(comments are locked)
10|1200 characters needed characters left

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 ( select Col1 from #Testtbl where ID =1 ) end else begin insert into [Test_DB].[dbo].TestTable ( select col1, col2 from #Testtbl where ID =2 ) end

drop table #Testtbl
more ▼

answered Jul 30 at 04:03 PM

chung gravatar image

chung
1

You are still trying to insert on TestTable. Understand, TestTable does not necessarily have [Col2].

I was suggesting you:

DROP TABLE TestTable; SELECT * INTO TestTable FROM #Testtbl;

Inside a transaction. However, I don't like your approach. I don't like what you're doing. I suggest you keep 2 versions of the script; or update your old database to the new schema.

Jul 30 at 04:59 PM virtualjosh
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108

asked: Jul 29 at 08:30 PM

Seen: 194 times

Last Updated: Jul 30 at 04:59 PM