a webpage that directly inputs data to a specific table in an sql database ?

im new to sql and ive created a test data base with a test table having 3 input fields i was wondering if it was possible to use a webpage to input data into the database in a specific table
ive seen some net articles discussing this but as i said im new they talked about using an asp page,so i was wondering if someone could help me out with a simple code to enable a webpage to input data on an sql table
so how do i go about it ?
test_database
dbo.test_table
the 3 input fields are name age gender

ive also set an odbc for the data base (test_database) set at native

tnx

Quote:

Originally Posted by arawsg

im new to sql and ive created a test data base with a test table having 3 input fields i was wondering if it was possible to use a webpage to input data into the database in a specific table
ive seen some net articles discussing this but as i said im new they talked about using an asp page,so i was wondering if someone could help me out with a simple code to enable a webpage to input data on an sql table
so how do i go about it ?
test_database
dbo.test_table
the 3 input fields are name age gender

ive also set an odbc for the data base (test_database) set at native

tnx

Hi ,

I don't know which platform you are using to develop your webpage.I give you sample code here to insert data into a sql server database table from an asp.net web page. I think this will help you.

---------
Using System.Data.SqlClient
void Page_Load()
{
SqlConnection con=new SqlConnection ("server=labserver;database=testdb;username=sa;passw ord=s");
SqlCommand cmd=new SqlCommand("insert into emptable(empid,empname) values(2312,'Adam')",con);
con.open();
cmd.ExecuteNonQuery();
con.close();
}
----------|||

Quote:

Originally Posted by balajiu

Hi ,

I don't know which platform you are using to develop your webpage.I give you sample code here to insert data into a sql server database table from an asp.net web page. I think this will help you.

---------
Using System.Data.SqlClient
void Page_Load()
{
SqlConnection con=new SqlConnection ("server=labserver;database=testdb;username=sa;passw ord=s");
SqlCommand cmd=new SqlCommand("insert into emptable(empid,empname) values(2312,'Adam')",con);
con.open();
cmd.ExecuteNonQuery();
con.close();
}
----------

tnx ill try your code out

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

If you want to determine type of trigger action from single trigger then see code below:

set nocount on
go
if object_id('tr_tbl') is not null
drop table tr_tbl
go
create table tr_tbl(i int)
go
create trigger tr_test on tr_tbl for insert, update, delete as
if exists(select * from inserted) and exists(select * from deleted)
print 'Update...' -- set @.type = 'U'
else
if exists(select * from inserted)
print 'Insert...' -- set @.type = 'I'
else
print 'Delete...' -- set @.type = 'D'

... your code
go

insert tr_tbl values(1)
update tr_tbl set i = i + 1
delete tr_tbl

go

drop table tr_tbl

go

But it might be easier writing separate triggers. And you shouldn't query the base table T1 directly since you will get all rows in the table and not just the rows affected by the DML. You need to use the inserted/deleted tables based on the trigger action.

|||

This helps a lot, thanks,

One question: since there are more than one user on the system will there be a case that insert virtual table might have more than one row?

|||Yes, you should always write your trigger code such that it handles multiple rows. This will also force you to use simple set of DMLs from trigger for the logic which is more efficient than a procedural approach. If you want to enforce only singleton operations via DML then you can do so within trigger by checking for @.@.ROWCOUNT and rolling back the transaction/ DML operation.sql

a trigger code

I have table T1 and I am trying to extract some date from T1 table based on inserts, updates and deletes. The destination table T2 has three fields F1, F2, F3, so I need something like

Insert into T2 (F1, F2, F3)

Select (F1, F2,Type)

From T1

Type should be defined based on Insert, Update, or Delete. This will be my first trigger, Can anyone write this trigger for me?

you already have the code you need. you just need to put it in a trigger for which the syntax can be found in BOL.

A simple calculation ?

I have rows of data in a VB DataGrid with the following fields

Product, Price/Unit, # of Units.

I want to be able to display these fields plus an extended value to calculate the Price/Unit * # of Units.

This seems like it should be easy, I'm just getting a little mixed up with the Syntax.

Any help would be appreciated.

Thanks in advance

tattoo

You are writing the syntax yourself.
select Product, PricePerUnit, NumberOfUnits, PricePerUnit * NumberOfUnits as TotalPrice
from ...|||Works perfectly thank you

A question about udf versus sp in a specific context...

Given the following objective:

1. Assume that I have a table that contains two fields: an auto-numbered id and an integer value
2. Check to see if a record exists in a table based on a parameter query of the integer value
3. If the record exists, return the record id
4. If the record does not exist, insert a new record into the table (using the parameter value as data) and return the auto-numbered id of the new record

I can do each of these things as a sequence of individual steps, of course, but it seems to me that I ought to be able to do it with a single udf (or perhaps a specialized query) that would be more efficient. I couldn't find something like this in the beginning SQL Express books I have on hand and I also didn't find anything exactly on point on this newsgroup or a search of Google. However, I am sure the answer is 'out there' and I am hoping that someone can point me in the right direction. Thanks!

Duncan

In thinking further about my original question and digging into one of my old SQL 2000 books, I concocted the following usp which seems to work:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT
AS
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF @.@.ROWCOUNT = 1
RETURN @.kit_id
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
RETURN @.kit_id
END

Does anyone see any particular issues with the above code? Thanks.

Duncan

|||

Perhaps something like this:

CREATE PROC [dbo].[usp_GetKitId] @.product_variant_id int, @.kit_id int OUTPUT AS
BEGIN
SET NOCOUNT ON

IF EXISTS(SELECT * FROM kits WHERE product_variant_id = @.product_variant_id)

BEGIN
SELECT @.kit_id = id FROM kits WHERE product_variant_id = product_variant_id
END
ELSE
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END

SET NOCOUNT OFF

END

|||Thanks for helping me out!|||

To give back in a small way, here is a slightly modified version of my earlier repsonse:

CREATE PROC [dbo].[usp_GetKitId] @.kit_id int OUTPUT, @.product_variant_id int
AS
SET NOCOUNT ON
SELECT @.kit_id = id FROM kits WHERE (product_variant_id = @.product_variant_id)
IF NOT @.@.ROWCOUNT = 1
BEGIN
INSERT INTO [dbo].[kits]([product_variant_id]) VALUES (@.product_variant_id)
SET @.kit_id = SCOPE_IDENTITY()
END
SET NOCOUNT OFF

Then called from code-behind like so:

PublicSharedFunction GetKitId(ByVal product_variant_id)AsInteger'If a kit exists for the product variant, return the kit id; if a kit does'not exist for the product variant, create a new kit and return the new'kit's id.Dim connectionStringAsString = ConfigurationManager.ConnectionStrings("atheniqueonlineConnectionString").ConnectionStringDim connectionAsNew SqlConnection(connectionString)Dim cmdAsNew SqlCommandDim kitIdAsIntegerWith cmd

.Connection = connection

.CommandText =

"usp_GetKitId"

.CommandType = CommandType.StoredProcedure

.Parameters.Clear()

.Parameters.Add(

New SqlParameter("kit_id", SqlDbType.Int))

.Parameters(

"kit_id").Direction = ParameterDirection.Output

.Parameters(

"kit_id").Value = 0

.Parameters.Add(

New SqlParameter("product_variant_id", SqlDbType.Int))

.Parameters(

"product_variant_id").Direction = ParameterDirection.Input

.Parameters(

"product_variant_id").Value = product_variant_idEndWithTry'Fill table

connection.Open()

Try

cmd.ExecuteNonQuery()

Catch exAs Exception'Handle exceptionsEndTryCatch eAs SqlException' Handle exceptionsFinally

connection.Close()

EndTry'Return the result

kitId = cmd.Parameters(

"kit_id").ValueReturn kitIdEndFunction

A problem with polish Chars.

Hi guys,

I got a big problem with my SQL 2000 DB, I got to enter polish chars in the fields and I don't seem to be able to. I tried setting the collation to SQL_Latin1_General_CP1250_CI_AS or SQL_Polish_CP1250_CS_AS or used the window collation in polish on the separate fields, I tried setting my new DB collation to SQL_Polish_CP1250_CI_AS. But nothing helped I tried to insert the phrase "Wyda?e? 200 punktów na gr? w ping-ponga." but every time the special chars like the "?" or the "ó" are gone and become normal "e" or "o".

Could somebody help me here cause I'm really not getting out of this one.

Hi!

Posted elsewhere by Dejan Sarka, SQL Server MVP :

"In SQL 2000 you can define collations on server, db and column level. This collation is used, for example if defined on column level for a varchar column, on all rows. If you need different collations in different rows of the same column, then you have to use unicode data types, in the example nvarchar. So, check the collation on all levels - server, db and column."

A problem with polish Chars.

Hi guys,

I got a big problem with my SQL 2000 DB, I got to enter polish chars in the fields and I don't seem to be able to. I tried setting the collation to SQL_Latin1_General_CP1250_CI_AS or SQL_Polish_CP1250_CS_AS or used the window collation in polish on the separate fields, I tried setting my new DB collation to SQL_Polish_CP1250_CI_AS. But nothing helped I tried to insert the phrase "Wyda?e? 200 punktów na gr? w ping-ponga." but every time the special chars like the "?" or the "ó" are gone and become normal "e" or "o".

Could somebody help me here cause I'm really not getting out of this one.

Hi!

Posted elsewhere by Dejan Sarka, SQL Server MVP :

"In SQL 2000 you can define collations on server, db and column level. This collation is used, for example if defined on column level for a varchar column, on all rows. If you need different collations in different rows of the same column, then you have to use unicode data types, in the example nvarchar. So, check the collation on all levels - server, db and column."

a problem with adding fields in ssems

hi,
i have a DB and it has some tables that the tables has related link (diagram).now when i wanna to change a table's field , the Sql Server errors that the table is not empty.when i try to delete the table's content , Sql server errors that the table is use a relation with another table.
so can i change a table's structure?
by the way before i forget , the Sql Server's error is below:

'UserManagement' table
- Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'isadmin' cannot be added to non-empty table 'UserManagement' because it does not satisfy these conditions.

thanks,
M.H.H

First, add the column to the table, allowing values to be NULL, or provide a 'starting out' (DEFAULT) value.

ALTER TABLE UserManagement
ADD IsAdmin int NULL
-->OR<--
ADD IsAdmin int DEFAULT 0

Then, update the table to provide the values you want the rows to have.

|||

hi,thanks for ur attention,

my problem is solved,

M.H.H

A problem on count when calculating business days

Here is the information regarding problem.

1) I have three fields in the detail section. They are calculated and displayed on the report from crystal side. The fields I am displaying are the release numbers which are invoiced with in 3 business days, invoiced more than 3 business days and Not invoiced more than 3 business days. The report is grouped by market field. When I am inserting summary for each field by market it is also counting the blank fields and displaying the count. The data that I was pulling from database is correct. I am unable to figure it out why it is counting the blank fields while calculating summary.

2) And the other problem is, it is displaying the blank spaces in the report when the condition fails.

For reference here I am inserting report . Any Help could be greatly appeaciated. Its a little bit urgent.Hi all

I figured it out my self.

A more efficient query

I am trying to add and subtract a few fields in a table to determine return
on investment
Currently i'm performing this as follows:
SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue,
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
ReturnOnInvestment
FROM
TableName
Is there a more efficient way of doing this, i am calling the same
calcutions twice so it seems there must be.
I tried setting variables to the costs and revenues, but multiple results
are being returned so this proved difficult.
Any help would be appreciated, thanks
You could try a derived table, but I seriously doubt you'll see any
performance increase:
SELECT
Cost, Revenue
(Revenue - Cost) as ReturnOnInvestment
FROM
(SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue
FROM TableName) x(Cost, Revenue)
"GrantMagic" <grant@.magicalia.com> wrote in message
news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> I am trying to add and subtract a few fields in a table to determine
return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
>
> Any help would be appreciated, thanks
>
|||Yeah, i tested the two methods against each other and there is no difference
between the two
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> You could try a derived table, but I seriously doubt you'll see any
> performance increase:
>
> SELECT
> Cost, Revenue
> (Revenue - Cost) as ReturnOnInvestment
> FROM
> (SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue
> FROM TableName) x(Cost, Revenue)
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> return
ColumnCost2))[vbcol=seagreen]
> as
results
>
|||You could try creating computed columns and index them. That might be quite
a bit faster...
ALTER TABLE TableName
ADD Cost AS (ColumnCost1 + ColumnCost2)
ALTER TABLE TableName
ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
ALTER TABLE TableName
ADD ReturnOnInvestment AS
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
ReturnOnInvestment)
-- You should probably try to make this into a covering index, with the
rest of the columns in your real query
"GrantMagic" <grant@.magicalia.com> wrote in message
news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> Yeah, i tested the two methods against each other and there is no
difference
> between the two
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> ColumnCost2))
> results
>
|||Thanks, i will give that a try.
Would i need to drop those columns after my query, or only create them once?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eBh6oPdkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> You could try creating computed columns and index them. That might be
quite
> a bit faster...
> ALTER TABLE TableName
> ADD Cost AS (ColumnCost1 + ColumnCost2)
> ALTER TABLE TableName
> ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
> ALTER TABLE TableName
> ADD ReturnOnInvestment AS
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
> CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
> ReturnOnInvestment)
> -- You should probably try to make this into a covering index, with the
> rest of the columns in your real query
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> difference
>
|||"GrantMagic" <grant@.magicalia.com> wrote in message
news:uD9r5idkEHA.556@.tk2msftngp13.phx.gbl...
> Thanks, i will give that a try.
> Would i need to drop those columns after my query, or only create them
once?
Only once, they'll be columns in your table after that, just like any
other column (except you won't be able to update them; they'll be
automatically computed when you insert or update the other columns)
|||GrantMagic,
These calculations are so basic and highly optimized for any CPU, that
there will be no way to create any significant performance gain by
rewriting the statement. The current cost of the calculation part is
simply too low (in comparison with I/O, network speed, logical reads,
etc.)
Gert-Jan
GrantMagic wrote:
> I am trying to add and subtract a few fields in a table to determine return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
> Any help would be appreciated, thanks
(Please reply only to the newsgroup)

A more efficient query

I am trying to add and subtract a few fields in a table to determine return
on investment
Currently i'm performing this as follows:
SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue,
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
ReturnOnInvestment
FROM
TableName
Is there a more efficient way of doing this, i am calling the same
calcutions twice so it seems there must be.
I tried setting variables to the costs and revenues, but multiple results
are being returned so this proved difficult.
Any help would be appreciated, thanksYou could try a derived table, but I seriously doubt you'll see any
performance increase:
SELECT
Cost, Revenue
(Revenue - Cost) as ReturnOnInvestment
FROM
(SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue
FROM TableName) x(Cost, Revenue)
"GrantMagic" <grant@.magicalia.com> wrote in message
news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> I am trying to add and subtract a few fields in a table to determine
return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
>
> Any help would be appreciated, thanks
>|||Yeah, i tested the two methods against each other and there is no difference
between the two
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> You could try a derived table, but I seriously doubt you'll see any
> performance increase:
>
> SELECT
> Cost, Revenue
> (Revenue - Cost) as ReturnOnInvestment
> FROM
> (SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue
> FROM TableName) x(Cost, Revenue)
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> > I am trying to add and subtract a few fields in a table to determine
> return
> > on investment
> >
> > Currently i'm performing this as follows:
> >
> > SELECT
> > (ColumnCost1 + ColumnCost2) as Cost,
> > (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> > ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 +
ColumnCost2))
> as
> > ReturnOnInvestment
> > FROM
> > TableName
> >
> > Is there a more efficient way of doing this, i am calling the same
> > calcutions twice so it seems there must be.
> >
> > I tried setting variables to the costs and revenues, but multiple
results
> > are being returned so this proved difficult.
> >
> >
> > Any help would be appreciated, thanks
> >
> >
>|||You could try creating computed columns and index them. That might be quite
a bit faster...
ALTER TABLE TableName
ADD Cost AS (ColumnCost1 + ColumnCost2)
ALTER TABLE TableName
ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
ALTER TABLE TableName
ADD ReturnOnInvestment AS
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
ReturnOnInvestment)
-- You should probably try to make this into a covering index, with the
rest of the columns in your real query
"GrantMagic" <grant@.magicalia.com> wrote in message
news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> Yeah, i tested the two methods against each other and there is no
difference
> between the two
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> > You could try a derived table, but I seriously doubt you'll see any
> > performance increase:
> >
> >
> > SELECT
> > Cost, Revenue
> > (Revenue - Cost) as ReturnOnInvestment
> > FROM
> > (SELECT
> > (ColumnCost1 + ColumnCost2) as Cost,
> > (ColumnRevenue1 + ColumnRevenue2) as Revenue
> > FROM TableName) x(Cost, Revenue)
> >
> >
> > "GrantMagic" <grant@.magicalia.com> wrote in message
> > news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> > > I am trying to add and subtract a few fields in a table to determine
> > return
> > > on investment
> > >
> > > Currently i'm performing this as follows:
> > >
> > > SELECT
> > > (ColumnCost1 + ColumnCost2) as Cost,
> > > (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> > > ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 +
> ColumnCost2))
> > as
> > > ReturnOnInvestment
> > > FROM
> > > TableName
> > >
> > > Is there a more efficient way of doing this, i am calling the same
> > > calcutions twice so it seems there must be.
> > >
> > > I tried setting variables to the costs and revenues, but multiple
> results
> > > are being returned so this proved difficult.
> > >
> > >
> > > Any help would be appreciated, thanks
> > >
> > >
> >
> >
>|||Thanks, i will give that a try.
Would i need to drop those columns after my query, or only create them once?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eBh6oPdkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> You could try creating computed columns and index them. That might be
quite
> a bit faster...
> ALTER TABLE TableName
> ADD Cost AS (ColumnCost1 + ColumnCost2)
> ALTER TABLE TableName
> ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
> ALTER TABLE TableName
> ADD ReturnOnInvestment AS
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
> CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
> ReturnOnInvestment)
> -- You should probably try to make this into a covering index, with the
> rest of the columns in your real query
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> > Yeah, i tested the two methods against each other and there is no
> difference
> > between the two
> >
> > "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> > news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> > > You could try a derived table, but I seriously doubt you'll see any
> > > performance increase:
> > >
> > >
> > > SELECT
> > > Cost, Revenue
> > > (Revenue - Cost) as ReturnOnInvestment
> > > FROM
> > > (SELECT
> > > (ColumnCost1 + ColumnCost2) as Cost,
> > > (ColumnRevenue1 + ColumnRevenue2) as Revenue
> > > FROM TableName) x(Cost, Revenue)
> > >
> > >
> > > "GrantMagic" <grant@.magicalia.com> wrote in message
> > > news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> > > > I am trying to add and subtract a few fields in a table to determine
> > > return
> > > > on investment
> > > >
> > > > Currently i'm performing this as follows:
> > > >
> > > > SELECT
> > > > (ColumnCost1 + ColumnCost2) as Cost,
> > > > (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> > > > ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 +
> > ColumnCost2))
> > > as
> > > > ReturnOnInvestment
> > > > FROM
> > > > TableName
> > > >
> > > > Is there a more efficient way of doing this, i am calling the same
> > > > calcutions twice so it seems there must be.
> > > >
> > > > I tried setting variables to the costs and revenues, but multiple
> > results
> > > > are being returned so this proved difficult.
> > > >
> > > >
> > > > Any help would be appreciated, thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||"GrantMagic" <grant@.magicalia.com> wrote in message
news:uD9r5idkEHA.556@.tk2msftngp13.phx.gbl...
> Thanks, i will give that a try.
> Would i need to drop those columns after my query, or only create them
once?
Only once, they'll be columns in your table after that, just like any
other column (except you won't be able to update them; they'll be
automatically computed when you insert or update the other columns)|||GrantMagic,
These calculations are so basic and highly optimized for any CPU, that
there will be no way to create any significant performance gain by
rewriting the statement. The current cost of the calculation part is
simply too low (in comparison with I/O, network speed, logical reads,
etc.)
Gert-Jan
GrantMagic wrote:
> I am trying to add and subtract a few fields in a table to determine return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
> Any help would be appreciated, thanks
--
(Please reply only to the newsgroup)

A more complicated case of insertion filtering by more than two fields

Hi,

I posted a problem some hours ago. I found that the solution that l was given by Karolyn was great, but at that time I didn't realize that my problem was a little bit more complicated. I'll rephrase my problem:

I need to insert some registers in a table. These registers have three fileds: col1, col2 and col3. I don't want to insert a register if in the table already exists a row with the col1, col2 and col3 combination of that register. These fields are PK, but I don't want to get errors. The problem is that I'm inserting a field that belongs also to the destination table. How can I filter a "destination" table by two fields in this case?

This the table1:

create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col1, col2, col3)
)

Here's my "insert" code:

INSERT INTO table1
SELECT table2.col1, table3.col2, table1.col3
FROM table2, table3
WHERE table2.col1 = table3.col1

The third field in the SELECT now refers also to table1. Witch conditions should I add to avoid repetitions in table1 (avoiding also erroing)

Thanks

FedericoOriginally posted by fmilano

INSERT INTO table1
SELECT table2.col1, table3.col2,table1.col3
FROM table2, table3
WHERE table2.col1 = table3.col1


Doesn't make sense....

A MergeJoin using two fields doesnt work !

Hi,

I have an issue. When I am performing a Merge Join using two fields of type
DT_WSTR on both sides of the Join, the Dataviewer after the Join only shows the left side 2 key
fields having values populated but both fields from the right hand side as being NULL. The Dataviewers I put in before the join show both sides of the join fields being populated ( 2 sides x 2 fields ). If I remove one of the fields from the join it shows both fields from the left side and the right side of the join being populated . I am trying to knock out duplicate values from the insert using 'genuine' Nulls from the right hand side so I need both parts of the 'Key'. I have checked the adnvanced tab that ignores Case etc but has had no effect.

I am using the technique from here : http://www.sqlis.com/311.aspx

Is this an SSIS bug ? It is driving me up the wall.

Help would be much appreciated please.

Thanks
Jon

SORTED !!

Sorry for this wasted post but this may be usefull for someone else in the future.

I changed the package to perform a lookup instead of a merge join and that failed as well. When I looked at the DB one of the two fields I was looking up to was defined as an NCHAR(15) and so always had whitespace at the end. I changed the datatype to be an NVARCHAR(15), reloaded the contents of the field and Voila, all was well.

Thanks anyway.

Jon