Is there something like rowtype in SQL Server 2008

Question

Is there something like rowtype in SQL Server 2008

matsinopoulos

Ten Centuries

Points: 1049
More actions
January 18, 2009 at 1:56 am

#97131

Hi,
I was wondering whether there is any similar construct in SQL Server 2008 with what one has in Oracle as ROWTYPE.
What I want to do is
1. to declare a cursor, e.g. DECLARE CURSORT X FOR SELECT * FROM MY_TABLE;
2. declare a local variable to be of the row type of the table MY_TABLE
3. fetch cursor into the local variable
...without having to hard-code all the columns of MY_TABLE into the definition of the local
variable that I fetch into.
I suppose that this would be possible if there were a ROWTYPE tool in SQL SERVER 2008.
Anything?
Thanks in advance
P.Matsinopoulos

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

matsinopoulos (1/18/2009)
Hi,
I was wondering whether there is any similar construct in SQL Server 2008 with what one has in Oracle as ROWTYPE.

No. You'll have to do it the hard way.

Aside, what are you using a cursor for?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

matsinopoulos Ten Centuries Points: 1049 More actions · Answer 2

Thanks for the answer.

For the aside...I want to do the following

I have two tables, table X and table Y, that I want to synchronize. They have the same data structure, i.e. columns, column types and order of columns and primary key. So,

(pseudo code)

1. for each row of a table X using cursor

update tableY set column1=cursor.row.column1, column2=cursor.row.column2 where

tableY.id = cursor.row.id

So, I would imagine it is quite common to use a cursor to traverse table X. No?

panayotis matsinopoulos

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

matsinopoulos (1/18/2009)
So, I would imagine it is quite common to use a cursor to traverse table X. No?

No!

That's a single update statement, no need to do it row by row. A row by row update of a couple million row tables would take absolutely hours.

UPDATE TableY

SET TableY.Col1 = TableX.Col2,

TableY.Col2 = TableX.Col2,

... Repeat for each column

FROM TableY INNER JOIN TableX On TableY.ID = TableX.ID

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

matsinopoulos Ten Centuries Points: 1049 More actions · Answer 4

Thanks you very much. I didn't know this sql update syntax.

Thanks again.

panayotis matsinopoulos