How to work around in T-SQL an Oracle %Rowtype similar var?

brunno_c · #1 (**permalink**) 07-19-02, 14:04

I'm working on a Oracle migration to SQL Server and now I need to know how to declare, a %rowtype like var type in SQL Server (T-SQL).

I know SQL Server does not accept %rowtype declaration, but I still need to retrieve an entire table row into one variable at once. As e.g.

------------

Tab1 is (
f1 int,
f2 datetime,
f3 varchar(50));

---------------

declare var1 Tab1%rowtype;

select * from Tab1 into var1 where f1 = 10;

insert into tab2 values (var1.f1, var1.f2, var1.f3);

--------

Any help is welcome,

Regards,
Bruno.

achorozy · #2 (**permalink**) 07-19-02, 16:20

With SQL Server 2000 you can DECLARE a variable of type table:
Using the sample PUBS database

Code:

DECLARE @xxx TABLE 
        (
	au_id char (11) NOT NULL ,
	au_lname varchar (40),
	au_fname varchar (20)
)

INSERT @xxx
SELECT
	au_id ,
	au_lname ,
	au_fname 
FROM	authors
WHERE   au_id = '341-22-1782'

SELECT au_lname FROM @xxx
GO

With pre-SQL Server 2000 and with SQL Server 2000 you can use temporary table:

Code:

CREATE TABLE #xxx
        (
	au_id char (11) NOT NULL ,
	au_lname varchar (40),
	au_fname varchar (20)
)

INSERT #xxx
SELECT
	au_id ,
	au_lname ,
	au_fname 
FROM	authors
WHERE   au_id = '341-22-1782'

SELECT au_lname FROM #xxx
GO

Otherwize you could could create seperate variables and select into them

Code:

DECLARE @au_id char (11),
	@au_lname varchar (40),
	@au_fname varchar (20)

SELECT @au_id = au_id ,
	@au_lname = au_lname ,
	@au_fname = au_fname
FROM	authors
WHERE   au_id = '341-22-1782'

SELECT @au_lname
GO

The third method works for SQL Server 7.0/2000 and 6.x. The two methods would allow you to initially select more than one record and then you could use a cursor on it. Or you can use the third method with a cursor. This may be a vague answer but from you example I don't see the benefit, but that's not for me to decide.

Internet Services

Web Development

Digital Marketing

Consumer Tech