inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satish
Hi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default...b;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command

create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')

when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067

how i can i view the image?

pls clarify my doubt

satishUse WRITETEXT/READTEXT instead of INSERT/SELECT to store / retrieve
There is quite a detailed explanation in Books Online

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141732028.000941.183610@.e56g2000cwe.googlegr oups.com...
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
>
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
>
> pls clarify my doubt
>
> satish|||satish (satishkumar.gourabathina@.gmail.com) writes:
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?

You have not inserted the the image into the table. You have inserted the
disk location of the image into the table. Run

SELECT convert(varchar(80), d) FROM aa

to see.

There is no way to insert data into a column directly from a file. The
normal way of loading image is write a program that reads the file,
and the passes the binary stream through a parameterised INSERT statement
in a client API. You can also convert the contents to a hexstring and
build an INSERT statement from that.

Conversly, to display the image you also need an application. If you
have stored an image in a table, a SELECT on that table in Query Analyzer
or Mgmt Studio will only display a long hex string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

inserted the image in a column-how can i view the image

hi,
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C7072756468766920
3030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/defaul...kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"satish" wrote:

> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C7072756468766920
3030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>sql

INSERT...SELECT and OUTPUT question

Why would this syntax be valid and the second one is not (below)?
begin tran
USE AdventureWorks
GO
DECLARE @.MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @.MyTableVar
--OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800 and 810;
--Display the results of the table variable.
SELECT ProductID, ProductName, PhotoID, ProductModelID
FROM @.MyTableVar;
GO
rollback
This is not valid. Why not? Am I missing something?
Is it that only with UPDATE/DELETE other fields from JOIN can be in output ?
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales money NOT NULL
);
GO
INSERT INTO dbo.EmployeeSales
OUTPUT INSERTED.EmployeeID,
INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales,
e.EmployeeID
SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.1
0
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GOFarmer (someone@.somewhere.com) writes:
> Why would this syntax be valid and the second one is not (below)?
> DELETE Production.ProductProductPhoto
> OUTPUT DELETED.ProductID,
> p.Name,
> p.ProductModelID,
> DELETED.ProductPhotoID
> INTO @.MyTableVar
> FROM Production.ProductProductPhoto AS ph
> JOIN Production.Product as p ON ph.ProductID = p.ProductID
> WHERE p.ProductID BETWEEN 800 and 810;
>...
> INSERT INTO dbo.EmployeeSales
> OUTPUT INSERTED.EmployeeID,
> INSERTED.LastName,
> INSERTED.FirstName,
> INSERTED.CurrentSales,
> e.EmployeeID
> SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD,
> sp.SalesYTD * 1.10
> FROM HumanResources.Employee AS e
>...
The syntax diagram in Books Online gives us:
<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
In the comments section we find:
from_table_name
Is a column prefix that specifies a table included in the FROM clause
of a DELETE or UPDATE statement that is used to specify the rows to
update or delete.
Thus, Books Online clearly says that you cannot use e.EmployeeID in the
OUTPUT clause of an INSERT statement.
Then remains the question why it is so. We look at the syntax diagram
for INSERT:
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
[ TOP ( expression ) [ PERCENT ] ]
[ INTO]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
[; ]
Note here that the SELECT statement appears in this grammar as a
derived table. A derived table has the property, that it does not
see things outside of if, and the outside cannot look in.
In looser terms, we can simply say that the OUTPUT clause is part of
the INSERT clause in a way that the SELECT statement is not, and thus
does not have visibility of what is in the SELECT statement.
For DELETE or UPDATE it's a different matter as the FROM clause are
part of the DELETE and UPDATE statments themselves.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you,
very good explanation on your part. I see it now. I should have read more
carefully.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD66F91C3BYazorman@.127.0.0.1...
> Farmer (someone@.somewhere.com) writes:
> The syntax diagram in Books Online gives us:
> <column_name> ::=
> { DELETED | INSERTED | from_table_name } . { * | column_name }
> In the comments section we find:
> from_table_name
> Is a column prefix that specifies a table included in the FROM clause
> of a DELETE or UPDATE statement that is used to specify the rows to
> update or delete.
> Thus, Books Online clearly says that you cannot use e.EmployeeID in the
> OUTPUT clause of an INSERT statement.
> Then remains the question why it is so. We look at the syntax diagram
> for INSERT:
> [ WITH <common_table_expression> [ ,...n ] ]
> INSERT
> [ TOP ( expression ) [ PERCENT ] ]
> [ INTO]
> { <object> | rowset_function_limited
> [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
> }
> {
> [ ( column_list ) ]
> [ <OUTPUT Clause> ]
> { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
> | derived_table
> | execute_statement
> }
> }
> | DEFAULT VALUES
> [; ]
> Note here that the SELECT statement appears in this grammar as a
> derived table. A derived table has the property, that it does not
> see things outside of if, and the outside cannot look in.
> In looser terms, we can simply say that the OUTPUT clause is part of
> the INSERT clause in a way that the SELECT statement is not, and thus
> does not have visibility of what is in the SELECT statement.
> For DELETE or UPDATE it's a different matter as the FROM clause are
> part of the DELETE and UPDATE statments themselves.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Farmer, try this one
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL
);
GO
CREATE TABLE #Temp ( EmployeeID int not null,
LastName nvarchar(20) NOT NULL)-
INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
SELECT e.EmployeeID, c.LastName
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
select * from #Temp
go
"Farmer" <someone@.somewhere.com> wrote in message
news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Thank you,
> very good explanation on your part. I see it now. I should have read more
> carefully.
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns976FD66F91C3BYazorman@.127.0.0.1...
>|||Thanks
You have missed my point though. This does not work and this can be a field
from a JOIN table from FROM statement.
OUTPUT e.EmployeeID, INSERTED.LastName INTO #Temp
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:O$1jqNeNGHA.3936@.TK2MSFTNGP12.phx.gbl
..
> Farmer, try this one
>
> IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
>
> DROP TABLE dbo.EmployeeSales;
>
> GO
>
> CREATE TABLE dbo.EmployeeSales
>
> ( EmployeeID nvarchar(11) NOT NULL,
>
> LastName nvarchar(20) NOT NULL
>
> );
>
> GO
>
> CREATE TABLE #Temp ( EmployeeID int not null,
>
> LastName nvarchar(20) NOT NULL)-
>
> INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
>
> OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
>
> SELECT e.EmployeeID, c.LastName
>
> FROM HumanResources.Employee AS e
>
> INNER JOIN Sales.SalesPerson AS sp
>
> ON e.EmployeeID = sp.SalesPersonID
>
> INNER JOIN Person.Contact AS c
>
> ON e.ContactID = c.ContactID
>
> WHERE e.EmployeeID LIKE '2%'
>
> ORDER BY c.LastName, c.FirstName;
>
>
>
> select * from #Temp
>
> go
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
>
>

insert within a function

CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END
CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END

I m getting error at the Insert statement, it says error 443, invalid use of insert within a function,

Cann we use insert in a function, if we cann, what is the alternative to insert the values?
do help me asap.Try moving the "Insert" into a stored procedure and then "exec procedure" from your function. Other solution would be to transform your function in a stored procedure by itself|||Did you look at BOL?

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.

And why are you define the same udf twice...and why isn't this a sproc?

insert values into both table at the same time using sql server 2005

hi all,

In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.

table 1:

tid--> int,identity,primary key

tname-->varchar(200)

table 2:

sid-->int,identity,primary key

tid-->fk (this tid is set as foreign key for the tid in table1)

now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..

pls help me..

thanks

swapna

Go For Stored Procedure for Insert...

SP Flow should be-

1. Start SQL Transaction

2. Insert into Table1

3. Get the Inserted INDENTITY value.

4.Insert into table 2

5. Commit Transaction Or Rollback transaction depending on the Error .

|||

You can create a stored procedure. There use will insert the record in the table 1 first and fetch the latest generated id value in table 1 using scope_identity and store it in a variable. Then you will insert the corresponding record in table 2 using the variable value.

For help on how to call a stored procedure from code visit http://forums.asp.net/t/1165758.aspx.

Feel free to ask for more help on this issue.

|||

Or you can create a trigger to insert the row into the second table.

It if must always happen the same way, the trigger would be a safer bet.

check out create trigger in the documentation.

sql

INSERT statement; only 1 column in table.. that too identity

need to write an insert statement to a table with only identity column
without using IDENTITY_INSERT option
create table t (id int identity(1,1) primary key)
RakeshRakesh
create table test(id int identity)
insert test default values
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
> need to write an insert statement to a table with only identity column
> without using IDENTITY_INSERT option
> create table t (id int identity(1,1) primary key)
> Rakesh|||Thanx
"Uri Dimant" wrote:

> Rakesh
> create table test(id int identity)
> insert test default values
>
>
> "Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
> news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
>
>|||create table t (id int identity(1,1) primary key)
INSERT INTO t default values
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
> need to write an insert statement to a table with only identity column
> without using IDENTITY_INSERT option
> create table t (id int identity(1,1) primary key)
> Rakesh

Insert SQL statament ?

I have a two table and they have relationship :

tblOrder :
+ OrderId : Type is AutoNumber(Identity) (PK)
+ CustomerID : Number(int)
+ OrderPrice : Number(int)
+ OrderDate : DateTime

and tblOrderDetail :
+ OrderID : Type is Number(FK)
+ ItemID : Number(int)
+ PriceEachItem : Number(int)

My problem : I want to insert data into two above table .
To tblOrder I use : insert into tblOrder(CustomerId,OrderPrice,OrderDate) values(.....) <--(This statement is allright )

So how i insert into tblOrderDetail when i can't not identify OrderID.

Please help me ! Thank u very much !

Insert into the order table, then use the SCOPE_IDENTITY() function to get back the key for that row...
DECLARE @.OrderID INT
INSERT tblOrder VALUES (...)
SET @.OrderID = SCOPE_IDENTITY()
INSERT tblOrderDetail VALUES (@.OrderId, ...)
|||

Thank adammachnic very much ! i am a beginner ! because i can only know "simple sql statement " i don't know clearly your sql statement ! How can i use them in Asp.net . Example in the my situation :

Sub Order_Click(a as Object ,e as EventArg) handles button1.Click
dim myconn as new sqlconnection("...")
dim sql as string ="Insert into tblOrder(...) values(...)"
dim mycmd as new sqlcommand(sql,myconn)
mycmd.executenonquery !
End Sub

I have never forgotten your advice ! Thank !

|||Actually, it would be better to put these statements into storedprocedures instead of calling them directly from your app. Youshould probably invest in a good book on SQL Server and ADO.NET. There are a few books available that cover both topics, that shouldhelp you out quite a bit!

|||

Thank adam ! I am going to study like you guide !

INSERT Record through a view

Is is possible to insert a record through a view. If so, how?

USE Northwind

GO

CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)

GO

ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO

CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)

GO

ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO

ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO

CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)

GO

ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO

ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO

CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms

GO

-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO

SELECT * FROM MyProducts
GO

DROP VIEW MyProducts
GO

DROP TABLE tbFlatPanelForms
GO

DROP TABLE tbDoubleTeeForms
GO

DROP TABLE tbForms
GO

Mike Bithink it's imposible to do with view with union
in your case you woudl like to insert data into 3 tables

maybe tray insert data into 2 tables and triger to put data into 3-th table|||you can insert into a view but you can only affect one table.
so in the case of unions this is not possible
BUT......
you can however use an instead of trigger to check for which table the insert is going to and then instead of inserting through the view, you insert directly to the correct table.|||BOL:

Updatable Partitioned Views
If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.

A view is considered an updatable partitioned view if:

The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).
The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.|||rdjabarov
no partition mentioned. so went with the conservative option

hey
how about some liquor this friday?|||Scott,

I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know.|||Originally posted by rdjabarov
Scott,

I am accompanying my daughter's class for the trip to NASA in Houston this evening. We're coming back on Friday night. But I hope it's gonna be shortly after noon, not at night. Will let you know. Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?

-PatP|||Hell...he'll be still backing out of his driveway by the time I get across NJ

:D

and a shamless 2500th post...

And you should look into partitioned views...the contraints have to be very specific...

but updating the base table is the best performing method...

for the view, the optimizer will still look at a tables in the view...|||Originally posted by Brett Kaiser
and a shamless 2500th post... Uff da! That's a lot of postings! Congratulations.

I still think you should have posted #2500 into the Yak Corral!

-PatP|||Originally posted by Pat Phelan
Now THAT's a road-trip! Funny, driving across Texas takes a lot longer than driving across Illinois, doesn't it?

-PatP

Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!|||Yeah, I had a car like that once...

;)

-PatP|||northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.

normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.

eternity ensues|||Originally posted by GDMI
Talking about long road trips .. Three years ago I drove all the way from the east coast to the west coast .. (NC to CA) .. and man! .. I thought it took me an eternity to drive across Texas!
Over the christmas holidays I drove a plymoth breeze from Windsor Ontario Canada -> South Padre Island, Texas -> Orlando Florida -> Back to Windsor

Just under 7000 kilometers if I remember correctly.

Great time though!

Mike B|||Originally posted by Ruprect
northern florida absolutely sucks
normally when you drive you will guage how long you have to go by the # of exits
for example when you get on a highway and your directions say to get off the highway at exit 120 and you are at exit 100.

normally that should take no time
but in northern FL the exits are 20 to 30 miles apart.

eternity ensues The best I can come up with is "Well duh!"

Why put the exits closer together? Who would want to get off? If you think that the highway inhales vigorously, you should have tried getting off the highway somewhere in northern Florida!

-PatP