SQL Server - Insert Not Working

Fri, 11 Jul 2008 13:09:00 -0700

Just wanted to share a bit of fun I had yesterday. (Actually it wasn't very fun). I had a cfquery that was supposed to copy "Template" records linked to one entity inserting them linked to another entity. So, the file was using an Insert/Select. Never seen one? OK, it looks like this.


	
		INSERT INTO
			[Template]
			(
				TemplateName,
				OwnerID,
				...
			)
		SELECT
			TemplateName,
			,
			...
		FROM
			[Template]
		WHERE
			OwnerID = 
			...

So, we can all agree that insert/select is a great thing. However, this particular one was not working (on the live server). I tried the normal things. I cut the SQL from the .cfm and pasted it into SQL Server Management Studio (SSMS), replaced the params with the values I knew it would be using and ran it (inside of a transaction, of course). Guess what? It worked. So, it works running in SSMS but, not in a cfquery. Hmmm... So, next, I added an "output" clause to the insert/select. That way I could see any records it would be inserting, just to make sure it really wasn't inserting them. Then I added a cfdump that would only run for me. "What's an output clause?" you say. Here I'll show you.


	
		INSERT INTO
			[Template]
			(
				TemplateName,
				OwnerID,
				...
			)
		OUTPUT
			INSERTED.*
		SELECT
			TemplateName,
			,
			...
		FROM
			[Template]
		WHERE
			OwnerID = 
			...

I ran that and guess how many records were inserted... 0. At this point I'm a bit frustrated. But, it begins to dawn on me that I've seen similar behavior before from SQL Server. There are time when the SQL Server will cache a bad plan for some SQL. In these cases, you can either have the database flush it's entire plan cache or you can alter the SQL enough to force it to make a new plan for your SQL. So, I added "AND 1=1" to my "where" clause. Guess what? It works now. This is a fairly edge case. I have not seen it very often. But, if you are positive that a cfquery is not returning what it should, before you jump out of your office window, try adding something innocuous to your "where" clause. If it does work, feel free to send some happy thoughts my way and curse MS under your breath. :-)

- SQL Server

SQL Server - Insert Not Working