rowid equivalence

Hi All,
Is there any equivalent to Oracle's rowid in sqlserver
What does Oracle's rowid do?|||What are trying to accomplish with the rowid?|||How do you get the row id? - I see it listed in the properties when I hover over a variable in debug mode but it never comes up in the intellisense and doesn't work when I try referencing it.sql

rowid equivalence

Hi All,
Is there any equivalent to Oracle's rowid in sqlserver
What does Oracle's rowid do?|||What are trying to accomplish with the rowid?|||How do you get the row id? - I see it listed in the properties when I hover over a variable in debug mode but it never comes up in the intellisense and doesn't work when I try referencing it.

'ROW_NUMBER' is not a recognized function name.

I am getting the following error while excuting following query in sqlserver 2005:

SELECT ProductName, UnitPrice,

ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank

FROM Products

ORDER BY UnitPrice DESC

if any one know what should be done to avoid this, please let me know.

Thanks in advance,

Rajanikanth.

Check and see if the database is running in SQL Server 2000 compatibility mode. Try running these two commands:

Code Snippet

select @.@.version

exec sp_dbcmptlevel 'yourDatabaseName'

|||

As Kent stated, check that you are connecting to a SS 2005 server. You could be using the client tools shipped with 2005, but if you connect to a 2000 instance, for example, then you will not be able to use the new features from 2005. The db compatibility level does not limit you from using the new features, if it is hosted in a 2005 instance.

How to identify your SQL Server version and edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;321185

AMB

sql

Row triggers equivalent in sql server 2000

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things

if anyone can help i would appreciate a lot!

RegardsFad,

This should work for you:

create trigger MyTrigger on MyTable for insert, update
as
begin
update MyTable
set Modification = getdate()
from MyTable m
inner join inserted i on m.MyTableID = i.MyTableID
end

-- Bill

"Fad" <fadyay@.gmail.comwrote in message
news:1169163810.969473.16880@.m58g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Hello Guys!
i have been working with oracle with quite a time. No i migrated to sql
server 2000 and i want to create a trigger on a table.
the trigger function has to update the Modification field to getdate()
whenever a row is being updated.
i tried lots of things
>
if anyone can help i would appreciate a lot!
>
Regards
>

|||Thanks AlterEgo for the help!
now i get the functionality of inserted and deleted tablessql

Row by row operations in sqlserver

hello,

I need to push data from a temporary table into a master table in sql server database(Both tables are in the same database).

I need to follow these conditions.
tables used:
1. temporary table: Temp
2. master table: Master

for every record or row in Temp
check if exists(Temp.field1)in Master.Field1 then
update Master with this row.
else Insert into Master this row.

I performed a research through the net, and found various suggestions. like usage of cursors, usage of while loops etc.
I have to use nearly 50,000 to 60, 000 rows minimum or even more.
Time complexity is also to be considered

I request all who visit this thread to place any possible solutions/suggestions how shall i make this task.

I thank all in advance
You could eithe ruse a cursor or another loop, like a While LOOP

BUT

i would rather use a setbased operation, because they are in common faster than the Loop or Cursor. So first update every record that exists in the database then insert the data that does not exists in the table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||Hello,
thanks for your effort, could you tell me a way to first update the data and then do a bulk insert into the master table,

Now im following like this:
1.Populate a table(say tblTemp) using open datasource(an Excel sheet),
2. the tblTemp is created by me on the fly each time the stored procedure(say sp1)
executes,
3. Another stored procedure(say Sp2) performs the update operation followed by Insert
like
while loop through the tblTemp
update the records which matches those in tblMaster.
While ends
"insert into tblMaster([Account Number],[Mobile Number],[Name]) select *
from tblTemp where [Account Number] not in ( select [Account Number]
from tblMaster)".
4. Process ends.
My main concenteration is on the time of execution as i have to manipulate a max of 60,000 or even more records at a time.

Hope you shall get me and please suggest any possible approach.

...thiru
|||

OK, assuming that you already have the loaded data in place in the temp table, you will need to have something like a primary or comparison key to find the assoviated records that already exist in the (to be updated) table. With some pseudo SQL code (because you did not provide any DDL that could be something like this (preventing any look or cursor)

CREATE TABLE TempTable
(
ID INT,
Col1 VARCHAR(50),
Col1 VARCHAR(50)
)

UPDATE ToBeUpdatedaTable
SET
Col1 = TT.Col1,
Col2 = TT.Col2
FROM TobeUpdatedTable TU
INNER JOIN TempTable TT
ON TT.ID = TU.ID

INSERt INTO ToBeInsertedTable
(
ID,
Col1,
Col2
)
SELECT

ID,
Col1,
Col2
FROM TempTable TT
WHERE NOT EXISTS
(
SELECT * FROM ToBeInsertedTable TI
INNER JOIN TI.ID = TT.ID
)

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

Row by row operations in sqlserver

hello,

I need to push data from a temporary table into a master table in sql server database(Both tables are in the same database).

I need to follow these conditions.
tables used:
1. temporary table: Temp
2. master table: Master

for every record or row in Temp
check if exists(Temp.field1)in Master.Field1 then
update Master with this row.
else Insert into Master this row.

I performed a research through the net, and found various suggestions. like usage of cursors, usage of while loops etc.
I have to use nearly 50,000 to 60, 000 rows minimum or even more.
Time complexity is also to be considered

I request all who visit this thread to place any possible solutions/suggestions how shall i make this task.

I thank all in advanceYou could eithe ruse a cursor or another loop, like a While LOOP

BUT

i would rather use a setbased operation, because they are in common faster than the Loop or Cursor. So first update every record that exists in the database then insert the data that does not exists in the table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Hello,
thanks for your effort, could you tell me a way to first update the data and then do a bulk insert into the master table,

Now im following like this:
1.Populate a table(say tblTemp) using open datasource(an Excel sheet),
2. the tblTemp is created by me on the fly each time the stored procedure(say sp1)
executes,
3. Another stored procedure(say Sp2) performs the update operation followed by Insert
like
while loop through the tblTemp
update the records which matches those in tblMaster.
While ends
"insert into tblMaster([Account Number],[Mobile Number],[Name]) select *
from tblTemp where [Account Number] not in ( select [Account Number]
from tblMaster)".
4. Process ends.
My main concenteration is on the time of execution as i have to manipulate a max of 60,000 or even more records at a time.

Hope you shall get me and please suggest any possible approach.

...thiru|||

OK, assuming that you already have the loaded data in place in the temp table, you will need to have something like a primary or comparison key to find the assoviated records that already exist in the (to be updated) table. With some pseudo SQL code (because you did not provide any DDL that could be something like this (preventing any look or cursor)

CREATE TABLE TempTable
(
ID INT,
Col1 VARCHAR(50),
Col1 VARCHAR(50)
)

UPDATE ToBeUpdatedaTable
SET
Col1 = TT.Col1,
Col2 = TT.Col2
FROM TobeUpdatedTable TU
INNER JOIN TempTable TT
ON TT.ID = TU.ID

INSERt INTO ToBeInsertedTable
(
ID,
Col1,
Col2
)
SELECT

ID,
Col1,
Col2
FROM TempTable TT
WHERE NOT EXISTS
(
SELECT * FROM ToBeInsertedTable TI
INNER JOIN TI.ID = TT.ID
)

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

routing to diff instance of SQL Server on same server

Remus,

thank you for your reply on (microsoft.public.sqlserver.notificationsvcs).

one more question :

Server_2 == Server_3

so I set up a route for db2:

CREATE ROUTE [rem_s2_route] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'rem_s2' , ADDRESS = N'TCP://192.168.0.2:4022'

to create route for db3 (sql express) I tried

CREATE ROUTE [rem_s3_route] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'rem_s3' , ADDRESS = N'TCP://192.168.0.2\sqlexpress:4022'

this failed, what am I doing wrong or did I lost the plot? How do I diff beteen more than one instance of sql server on the same server? And can the port be the same on each instance of SQL Server?

thank you in advance

solved my own problem:

to create route for db3 (sql express) I tried

CREATE ROUTE [rem_s3_route] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'rem_s3' , ADDRESS = N'TCP://192.168.0.2\sqlexpress:4022'

changed to :

CREATE ROUTE [rem_s3_route] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'rem_s3' , ADDRESS = N'TCP://192.168.0.2:4023'

and I changed the endpoint on sqlexpress to listen on 4023

so ports between sql server instances need to diff on same server.