|
Blue_Boy wrote: You can't insert data into View.
Actually, most modern databases support what is called "updatable views". If your view does not contain calculated columns and the base tables have a default defined on the columns not selected in the view, then the view must be updatable.
|
|
|
|
|
|
A "view" is a way of looking at data stored in table(s), therefore using a view instead of the underlying base table will not solve your performance issue. If your query is slow, consider these tips:
1. Index your base table(s).
2. Use a WHERE clause to filter only those rows that is needed.
3. Select only the required columns (don't use a * in the SELECT statement).
|
|
|
|
|
i have create a function in pubs data base
create function show(@temp varchar(11))
returns table
return(select *from author where au_id=@temp)
now i wants to access this function from aspx page using c#
please give me an applicable answer so that i will satisfied
thanks
regards
(manoj bhatt)
|
|
|
|
|
String inputValue;
String sqlText;
SqlConnection connection;
SqlCommand command;
SqlDataReader dataReader;
inputValue="123ABC";
sqlText="SELECT dbo.show(" + inputValue +")";
connection=new SqlConnection(" ...whatever your connection stuff is ");
try
{
connection.Open();
command=new SqlCommand(sqlText,connection);
SqlDataReader dataReader = command.ExecuteReader();
if(dataReader != null)
{
while(dataReader.Read())
{
--get values from dataReader
}
}
}
catch
{
}
|
|
|
|
|
oooh! shiny! SQL injection, here we come!!!!
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
I know.... personally I would have preferred a stored procedure.
|
|
|
|
|
I have a couple of newbie questions for SQL Server. On the SQL Server Management Studio, I used the following script to delete and create a table on the master databse:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PARTNOTE]
GO
CREATE TABLE [dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I can run this as many times as I want. Both deletion and creation work well. My first question is "After the creation, where is the table creates?" I couldn't find where the table object from Object Explorer.
Now I modify the script to perform the same tasks not on the master, but on "MyDatabase":
if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [MyDatabase].[dbo].[PARTNOTE]
GO
CREATE TABLE [MyDatabase].[dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The creation still works but the deletion does not. It couldn't find where the table was creates.
Could someone please explain what goes wrong in the modified script? Thanks!
Best,
Jun
|
|
|
|
|
After you execute script, refresh Tables node by right click on it and click refresh, it should shows created tables.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thanks. The table created shows up after the refreshing.
Another question. My modified script
if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [MyDatabase].[dbo].[WL_HEADU]
GO
cannot detect the existence of the table I created. If I just execute
drop table [MyDatabase].[dbo].[WL_HEADU]
GO
it works well. What is a proper query I should make in order to detect the table created in "MyDatabase"?
Best,
Jun
|
|
|
|
|
Look to the if statement. Check what
select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 gives you when the file is known to exist. I have 2008 and it uses sys.object and I do not remember how to check for exists from prior.
|
|
|
|
|
Figured it out...adding
USE [MyDatabase]
GO
before the query can detect the table created.
Best,
Jun
|
|
|
|
|
That's great 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
The table is on the cloud
|
|
|
|
|
Jun Du wrote: create a table on the master databse:
You need to learn to create your own database and then tables go in that.
And once you do delete everything that you created in 'master'.
|
|
|
|
|
Hi All,
I have two table like this
Table1
-------
empcode basic salary
------- --------------
160 3000
170 4000
Table 2
---------
Amount Additions empcode
------ ---------- -------
180 Bonus 160
25 Transport 160
so i want the output like this
empcode basic Salary Bonus Transport
------- ------------ ----- ----------
160 3000 180 25
179 4000 175 45
Please help me with this ..I am new to DB
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
Looks like you need a join.
|
|
|
|
|
Thanks
My Table 2 is dynamic value ..so header should change based on Addition column
Table 2
---------
Amount Additions empcode
------ ---------- -------
180 Bonus 160
25 Transport 160
Ramkumar
("When you build bridges you can keep crossing them. ")
http://ramkumarishere.blogspot.com
|
|
|
|
|
Assuming you are using SQL Server.
I would suggest that you read up on pivot queries. MSDN: Pivot Query[^]
Nagy Vilmos wrote: And eat bacon. Bacon's real important for 'puters.
|
|
|
|
|
Assuming SQL Server, You can achieve this using the following PIVOT
select empcode, basicSalary, ISNULL(Bonus,0) as Bonus, ISNULL(Transport,0) as Transport
from
(SELECT s.empcode, s.basicSalary, a.description, a.amount
FROM salary s
LEFT JOIN additions a
ON s.empcode=a.empcode ) AS SalaryWithAdditions
PIVOT(
SUM(amount)
FOR description IN ([Bonus],[Transport])
) AS PivotTable
Output with your test data:
empcode basicSalary Bonus Transport
160 3000 180 25
170 4000 0 0
|
|
|
|
|
Check the following query...
SELECT T1.empcode,T1.[basic Salary],B.Bonus,T.Transport
FROM Table1 T1
INNER JOIN (SELECT Amount As Bonuus, empcode FROM Table2 WHERE Additions = 'Bonus') B ON B.empcode = T1.empcode
INNER JOIN (SELECT Amount AS Transport, empcode FROM Table2 WHERE Additions = 'Transport') T ON T.empcode = T1.empcode
Adjust the inner join to left join if required...
Thanks
|
|
|
|
|
I am trying to retrieve the Earliest and latest Dates and prices
Using the following code, which works fine. I need to enter the values to a variable
as seen below, which does not work can someone please help
Thanks in advance,
Michael
USE "Sales"
GO
SET NOCOUNT ON;
DECLARE
@dtEarliestDate DATETIME,
@fltEarliestPrice REAL,
@dtLatestDate DATETIME,
@fltLatestPrice REAL
SELECT * FROM
(
SELECT TOP 1 dtDateTime, fltPrice FROM Customers ORDER BY dtDateTime DESC
UNION ALL
SELECT TOP 1 dtDateTime, fltPrice FROM Customers ORDER BY dtDateTime ASC
)
AS B
--SET @dtEarliestDate = B.dtDateTime
--SET @dtLatestDate = B.dtDateTime
--SET @fltLatestPrice = B.fltPrice
--SET @fltEarliestPrice = B.fltPrice
|
|
|
|
|
This SQL works for me ...
begin
declare @max_date datetime
set @max_date = (select max(creation_date) from fsformula)
print @max_date
end
|
|
|
|
|
|
I haven't tested this but try it .
SELECT TOP 1 @dtLatestDate=dtDateTime, @fltLatestPrice =fltPrice FROM Customers ORDER BY dtDateTime DESC
SELECT TOP 1 @dtEarliestDate =dtDateTime, @fltEarliestPrice=fltPrice FROM Customers ORDER BY dtDateTime ASC
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
