Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

insert into View

Posted on 2005-03-25
29
Medium Priority
?
7,663 Views
Last Modified: 2010-06-01
** Table **

Create Table UPS_TEST
(
UserID nvarchar(100),
Age int NULL Default(0)
)


** View on that Table **

Create View UPS as
Select
      Convert(varchar(100), UserID) UserID,
      Age
From
      UPS_TEST


And Then...

insert into UPS(UserID, Age)
Values('1234567', 56)

I receieve the error...

Update or insert of view or function 'UPS' failed because it contains a derived or constant field.


Problem is with the CONVERT into the View. I can not  use Convert Function in View.l

0
Comment
Question by:khansoul
  • 11
  • 9
  • 5
  • +2
29 Comments
 
LVL 4

Expert Comment

by:imperial_p79
ID: 13631746
why do you want to insert into the View? why not in the table instead?

although you can insert into a view, strictly they are meant for a read purpose in a different view (thats why they are called so)

Hope this helps!
0
 

Author Comment

by:khansoul
ID: 13631762
The Problem is we are using DHL Shipping Service for Shipping and their Software can not read/write to the nvarchar DataTypes. and I want to Read/Write the values by the same table, by converting nvarchar to varchar (they support varchar) . they dont support unicode nvarchar for reading/writing I believe.

Thanks
0
 
LVL 4

Assisted Solution

by:imperial_p79
imperial_p79 earned 240 total points
ID: 13631809
AFAIK, you wont be able to achieve this thru a view.

Instead have an intermediate table which will be exposed to the shipping service which has a column of type varchar(100) and then write a insert trigger in this table that will convert and save it to your main table UPS_TEST

Hope this helps!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Expert Comment

by:ptjcb
ID: 13631833
You should not need to convert between varchar and nvarchar. It is an implicit conversion within SQL Server. Take the CONVERT statement out of the view and you should be fine.
0
 

Author Comment

by:khansoul
ID: 13631845
So what you recommending is the Following....

Have DHL Write to the Intermediate Table (With varchar DataTypes)
Have a Trigger on That Table to insert into the Production Table.
From Intermediate varchar to Production nvarchar should not be a problem?

And any idea why that problem is happening, i meaning without having convert in there its fine.
0
 

Author Comment

by:khansoul
ID: 13631852
ptjcb I know but I can not.
DHL Service does not support UNICODE nvarchar.
0
 
LVL 4

Expert Comment

by:imperial_p79
ID: 13631859
anyway your shipping service is not going to insert any double byte characters. so it would be a plain text in the intermediate table. (this intermediate table is introduced just because you mentioned that the shipping service cannot insert into a nvarchar datatype field)

then you just have a simple insert into main prod table.

Hope this is clear
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13631871
What version of SQL Server?
0
 

Author Comment

by:khansoul
ID: 13631872
yea I just wanted to make sure, SQL itself could careless for inserting from varchar to nvarchar(with Trigger)
0
 

Author Comment

by:khansoul
ID: 13631876
SQL Server 2000.
Again issue is with DHL  unicode nvarchar@ptjcb
0
 
LVL 4

Expert Comment

by:imperial_p79
ID: 13631884
it wont cause any problem. because the data in varchar will and can ALWAYS be inserted into an nvarchar column in sql server 2000
0
 

Author Comment

by:khansoul
ID: 13631919
ok in my view i will use the Convert Function for Readinding data then.
You Think View will Take care of conversation of nvarchar to varchar if I am reading that View off DHL machine?

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13631922
>>And any idea why that problem is happening, i meaning without having convert in there its fine.<<
Because it is a calculated column.
0
 

Author Comment

by:khansoul
ID: 13631938
> Because it is a calculated column.
how is it a calcluated column. you can see my CREATE TABLE statement.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13631941
Or to put it more plainly and repeat a much used analogy:  You cannot convert hamburger into a cow (The reverse is obviously true).
0
 

Author Comment

by:khansoul
ID: 13631956
acperkins thats really a good one, LOL.

but Reading that Column within View should be fine with the CONVERT function (to varchar from nvarchar)?

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13631957
>>how is it a calculated column.<<
I don't care about your table:
1. UPS is a view right?
2. UserID in that View is a consequence of applying a function, correct?

If you answered yes to the previous two questions then it is a calculated column.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13631978
To put it another way, you can certainly do this:
insert into UPS_Test(UserID, Age)
Values('1234567', 56)

User ID is now a column (and not a computed column) in the UPS_Test table, therefore you can update it without getting an error.  But I realize that is not what you want...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13631995
Actually the error message you got is more then (if that is possible) self-explanatory:
"Update or insert of view or function 'UPS' failed because it contains a derived or constant field."
0
 

Author Comment

by:khansoul
ID: 13632004
Thats Correct.

But I just tested. if i use convert within View it works fine for "Reading" but does not work for "Writing".
So i believe i will go with the Trigger Trick by having an intermediate Table.
but if we can read of the view(with Convert) then "Hamburger and Cow" Example is confusing:)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13632040
If you are bound and determined to maintain two separate values for UserID (one nvarchar and the other varchar) than instead of creating a separate table with a trigger, create a another column nUserID and have a trigger on the table to update one or the other value, depending which is updated.  Let me know if you want to take this approach. Just understand (that much like the previous bad analogy) you are going to lose something when you go from varchar to nvarchar and vice-versa.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 240 total points
ID: 13632072
>>if i use convert within View it works fine for "Reading" but does not work for "Writing".<<
That is correct.  Think of it this way:
USP_Test (table) = Cow
USP (view) = Hamburger

Reading is getting data from Table to View and in the process translate some value (Cow to Hamburger): OK
Writing is sending data from View to Table but need to translate back to the original value (Hamburger to Cow): Not OK

As you can see this analogy is wearing out very fast.

0
 

Author Comment

by:khansoul
ID: 13632074
userid was just a sample in production i have 15+ Fields all nvarchars and DHL recommened to  have a VIEW.
0
 
LVL 4

Expert Comment

by:imperial_p79
ID: 13632081
then you may as well go with creating a separate table with insert trigger option.
0
 

Author Comment

by:khansoul
ID: 13632107
I agree with both acperkins and imperial.
i will read data off the View and Insert into Intermediate table and Runa Trigger.

Thansk for the info guys.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13632191
>>userid was just a sample in production i have 15+ Fields all nvarchars<<
And you have a requirement for nvarchar data types because you are using a double-byte language such as Chinese, right?  Otehr wise they are a waste of space and cause more problems (such as this) than are worth it.

>>i will read data off the View and Insert into Intermediate table and Runa Trigger.<<
I don't see any need for an intermediate table.  But whatever works for you.
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 13633018
so varchar is better then nvarchar. but nvarchar holds 8000 characters.

if I dont do any intermediate table then you suggest me to have 15 extra fields within same tables.
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 13633039
BTW, Co-Workers.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13633178
>>so varchar is better then nvarchar<<
It depends, that is like asking if integer is better than tinyint.

>>but nvarchar holds 8000 characters<<
Actually only 4000.  varchar can hold 8000

>>if I dont do any intermediate table then you suggest me to have 15 extra fields within same tables.<<
That would be my prefrence, but it is up to you to decide.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question