Oracle's ROWTYPE equivalent in Sql Server 2014

Question

Oracle's ROWTYPE equivalent in Sql Server 2014

BOR15K

SSCertifiable

Points: 5894
More actions
June 11, 2018 at 6:11 am

#369678

I appreciate ROWTYPE equivalent didn't exist in SQL Server versions prior 2014 and I could find no information re SQL 2014 either
Can one advise, please? Perhaps there is some close alternative in 2014? I need to extract quite a lot of columns (over 50) from a relatively small table (no more than 10,000 records).

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

SQL doesn't have a rowtype equivalent.
ROWTYPE , in sql terms for me, is used in a cursor as a collection of all the @parameters that would correspond to each column.

in practice, fifty columns would need fifty parameters for the cursor, whereas Oracle has the ROWTYPE colleciton as an option.

In SQL Server, there's no need to usea cursor for data operations. everything should be set based operations instead.
you would simply do something like
SELECT * FROM dbo.SourceTable WHERE SomeCriteria=1 and all the rows would render. no need for a cursor.

the same for inserting or updating the data would apply, no cursor.

INSERT INTO TargetTable
SELECT * FROM dbo.SourceTable WHERE SomeCriteria=1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

Lowell - Monday, June 11, 2018 6:20 AM
SQL doesn't have a rowtype equivalent.
ROWTYPE , in sql terms for me, is used in a cursor as a collection of all the @parameters that would correspond to each column.
in practice, fifty columns would need fifty parameters for the cursor, whereas Oracle has the ROWTYPE colleciton as an option.
In SQL Server, there's no need to usea cursor for data operations. everything should be set based operations instead.
you would simply do something like
SELECT * FROM dbo.SourceTable WHERE SomeCriteria=1 and all the rows would render. no need for a cursor.
the same for inserting or updating the data would apply, no cursor.
INSERT INTO TargetTable
SELECT * FROM dbo.SourceTable WHERE SomeCriteria=1

Thank you for the quick reply. An only trouble that the table has over 100 columns, hence SELECT * FROM won't help in this case. Will have to manually select 50 columns then.

Lowell SSC Guru Points: 323518 More actions · Answer 3

in SSMS, if you drag the columns folder from the SQL object explorer to an ssms window, all columns will appear as a comma delimited list.
it's easier then to remove the columns you do not need.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

Lowell - Monday, June 11, 2018 6:30 AM
in SSMS, if you drag the columns folder from the SQL object explorer to an ssms window, all columns will appear as a comma delimited list.
it's easier then to remove the columns you do not need.

Using SSMS, I can drag one by one all the 50 columns, rather than deleting those I do not require. Seems to be faster. Thank you.