?
Solved

insert into View

Posted on 2005-03-25
29
Medium Priority
?
7,660 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 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