December 6, 2007 at 7:35 am
I have a new table with one column defined as a uniqueidentifier. I have a view with the same structure as the table that I would like to use as the data source to populate the table. The view sets the value of the uniqueidentifier to NULL.
I have a stored procedure that truncates my destination table, then excutes this statement
insert into destination_table select * from source_table
I get the following error when this executes
Msg 206,Operand type clash: int is incompatible with uniqueidentifier
Level 16, State 2, Procedure GIS_CONV_PopulateBlockTables, Line 16
What does this mean? Why is it even mentioning an int? There's no int involved. My table field is the uniqueidentifier. My corresponding view field value is set to null.
If I change the view to set the field to NEWID(), then the stored procedure runs. I don't want to populate that field at the time I populate the table.
What's going on?
December 6, 2007 at 8:23 am
You can make uniqueidentifiers null. So the problem lies somewhere else. Do you have a trigger on the table or a default on the column that would be causing this? Are you sure the column order on the insert and the select are correct? Are you sure the view is returning NULL and not a zero or something?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2007 at 8:43 am
You should not use * in queries, the order of columns in view and table may be physically different.
you must use
insert dest (col_a, col_b, unique_col) select cola, colb, null from your_view
this works
create table dest (un uniqueidentifier, acol int default(0))
create table tv (un1 uniqueidentifier not null default newid(), a_col int )
go
create view v as select * from tv
go
insert tv values (newid(), 0)
insert tv values (newid(), 1)
select * from v
insert dest select * from v
select * from dest
go
drop view v
drop table tv
drop table dest
this is not working - look at the order of fields in tables
create table dest (un uniqueidentifier, acol int default(0))
create table tv ( a_col int , un1 uniqueidentifier not null default newid())
go
create view v as select * from tv
go
insert tv values (0, newid())
insert tv values (1, newid())
select * from v
insert dest select * from v
select * from dest
go
drop view v
drop table tv
drop table dest
HTH
Piotr
...and your only reply is slàinte mhath
December 6, 2007 at 10:18 am
In my view, the part of the select has that deals with that guid field has
NULL AS SPAuditID
I know my fields are all in corresponding order in my insert statement, because if I change the select to
NEWID() as SPAuditID
the stored procedure runs.
I am using a * in the insert statement because this one of several conversion procedures that I will be using, we're moving data from one database into another and I don't want to have to start using field names in the insert.
December 6, 2007 at 11:05 am
Did you get a chance to check the destination table for default values or triggers? If you supply a number and the stored proc works, I would believe you have a default value set or a trigger that checks for null and auto populates the column.
December 6, 2007 at 11:16 am
I agree. But I do think you're climbing out on a limb to dismiss the use of column names. Yes, they're a pain the tucas to type, but you could use one of the many code generation tools to do it for you or use SQLPrompt from Embarcadero (type ahead for TSQL). Better that extra bit of work than to rely on the good will of the query engine to not return the columns in a different order.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2007 at 11:31 am
I still have the column order sitting in the back of my mind. Cmayeux feels so passionately about the column order being correct that I would like to exhaust all of the other options first.
December 6, 2007 at 11:59 am
True, the fact that it's getting the error when dealing with NULL certainly suggests a default or a trigger.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2007 at 12:19 pm
I have no defaults or triggers on my output table.
I've scripted out my table definition as a Create Table statement and have attached it here. Maybe that will shed some light.
I don't want to use column names, because we're in a state of flux with creating our new database and the column names will possibly change over the next few months. I don't want to constantly be modifying my stored procedures. This is the first stored procedure inserting data into the first table. I need to resolve this so I can move on. I'll have probably 20 or so main tables that I'll be creating this way and transferring data into. The structures of the tables are changing, so I think the view is the best way to extract the data then map directly into the new tables. These are strictly conversion routines, they will never be used again once we migrate to our new database.
At this point, I've modified the stored procedure to run an Update query after the Insert query that sets the value of the uniqueidentifier field to NULL. That worked.
However, I'd like to know why that error is occurring.
Thanks to everyone.
December 6, 2007 at 12:37 pm
Thanks for the code. I made a copy of your table and ran an insert, using NULL values for both the GUID's that were defined. It worked fine. The only difference I can see is that I did lay out the columns in the INSERT query.
I'm back to this being a problem with column order and the use of the SELECT *.
But, here's what I did. I created a second table using your script. I loaded it with data. I then created a view that did a SELECT * from the table. I ran the following script:
INSERT INTO [GIS_LAY_BlockHdr]
SELECT * FROM [guidview]
Went off without a hitch...
Something is either changing that data as it comes in, or you've got an issue with column order (that I'm not able to replicate).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2007 at 12:44 pm
Thanks for testing it out.
I don't see how the column order can be playing a part, because, if I set the value of SPAuditID to NEWID() in the view, then run the insert, it works perfectly. It's only if I set the value to NULL that it errors.
A bit more information, if I copy a record generated from the view (that contains NULL in the SPAuditID field), I can paste it directly into the table. That tells me that the NULL that's being generated by the view is a valid NULL value, and that the table can accept it.
Crazy!
December 6, 2007 at 12:49 pm
Yeah, something odd is going on.
Hmmm.... If you short circuit the event, skip the view and use the query from the view directly... same issue?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2007 at 1:02 pm
Grant is right we need to isolate the issue to either the insert or the view. If successfully insert outside the view, we can narrow the problem to the view.
December 6, 2007 at 1:44 pm
If I substitute the complete SQL of the view in the insert,
Insert into output_table
Select distinct TOP (100) PERCENT field1, field2, NULL as SPAuditID
from input_table
it inserts the records, with the NULL value, into the output_table.
So, it appears the problem is using Select * from theView.
December 6, 2007 at 2:20 pm
Did you list all 23 columns like the sample text file, or did you lessen the number of columns for testing?
When you plugged in newid() for the view insert did you confirm that this went to the correct column? Is it possible that they columns were misaligned and you were thinking that the insert worked properly while in fact it did not.
I have not heard of an issue where you cannot INSERT from a view using SELECT *, but that does not mean it does not exist.
Try to insert all columns as they exist in the view, if you have not done so already.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply