Insert Default Values Via View

Question

Post reply

Insert Default Values Via View

CrazyMan

SSChampion

Points: 12560
More actions
December 15, 2008 at 8:41 am

#155024

Hi All
I Need to create a view that inserts a default value into a table,
Eg
Table 1
Name Not Null
Location
Create View Sample1
Select * from Table 1
I need a view that will insert only Location and i need to define Name as a constant value inside the View,
Insert into Sample 1 (Location) values 'added'
When we run this this will through an exception that Name cant accept Null Values
Can i define name some how inside the view, if yes how this can be done
Thanks in advance
🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Why aren't you using a default value on the field?

[font="Courier New"]CREATE TABLE tmp (Name VARCHAR(10) NOT NULL DEFAULT(''), Location VARCHAR(10))

GO

CREATE VIEW Sample1 AS

SELECT * FROM tmp

GO

INSERT tmp (Location) VALUES ('My Loc')

GO

SELECT * FROM tmp

GO

DROP VIEW Sample1

GO

DROP TABLE tmp

GO[/font]

CrazyMan SSChampion Points: 12560 More actions · Answer 2

Thanks Michael

But the problem was that i cant insert null as default, since i need to create like 5 views with 5 default values 🙁

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

First of all let me state that I am not sure of what you want to do, but if it is to create 5 views each with a different default value for a particular column in the view then you may want to look at this.

/* this is your basic table which you want to base the view on with a default value for the name column*/

CREATE TABLE tmp (Name VARCHAR(10) NOT NULL DEFAULT(''), Location VARCHAR(10))

GO

INSERT tmp (Location) VALUES ('My Loc')

INSERT tmp (Location) VALUES ('Next Loc')

GO

/* Create a Table to hold the default values*/

CREATE TABLE tmp2 (Keyid INT IDENTITY (1,1),DefValue VARCHAR(50))

GO

INSERT tmp2 (DefValue) VALUES ('Default value #1')

INSERT tmp2 (DefValue) VALUES ('Default value #2')

GO

CREATE VIEW Sample1 AS

SELECT tmp.Location, Tmp2.DefValue

FROM tmp,tmp2

WHERE tmp2.Keyid = 1

GO

CREATE VIEW Sample2 AS

SELECT tmp.Location, Tmp2.DefValue

FROM tmp,tmp2

WHERE tmp2.Keyid = 2

GO

SELECT * FROM sample1

SELECT * FROM sample2

The results would be:

From View Sample1

Location DefValue

---------- --------------------------------------------------

My Loc Default value #1

Next Loc Default value #1

(2 row(s) affected)

From View Sample2

Location DefValue

---------- --------------------------------------------------

My Loc Default value #2

Next Loc Default value #2

If this is NOT what you are looking for, please post your question again.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Thanks Mate, it looks somthing like this, but the problem is that we are using hibernate to insert values into a table, As per my develolper he says SP cant be used on Hibernate , so he needs view to insert into the table, but we are planning to depoly the same application for variois cleints, so each client has one ID which has to be hardcoded on the view,the the other values can be inserted into.

Your post gave me an idea of how we can overcome the issue which we are facing, let me try this and let know what happned

Thanks 🙂