Question About Table Variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Question About Table Variables

Author

Topic

harissarwar
Starting Member

15 Posts

Posted - 2008-07-07 : 04:08:28

How can i access the first row in the table variable?
I want to change the value of a certain column in the first row of a table variable.
How can i achive this?
I dont have any criteria for selection. I simply want to access the first row and change a certain column value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 04:38:08

Do you have an id field or any field with unique value?

harissarwar
Starting Member

15 Posts

Posted - 2008-07-07 : 07:40:10

yes i have a field with a unique value but i haven't specified any primery key while making the table. But i can make a primery key. How this will be helpful in getting first row?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 07:45:15

Say you had the table definition


DECLARE @myTable TABLE (
[id] INT IDENTITY(1,1)
, [someColumnA] VARCHAR(50)
)

Then when you do an INSERT...


INSERT INTO @myTable (
[someColumnA]
)
SELECT 'foo'
GO

INSERT INTO @myTable (
[someColumnA]
)
SELECT 'bar'
GO

Then the insert for foo gets and Id of 1 (and becomes your first row).

And when you want to query / update the first row you would do


UPDATE @myTable
SET
[someColumnA] = 'newFoo'
WHERE
[Id] = 1

-------------
Charlie

harissarwar
Starting Member

15 Posts

Posted - 2008-07-07 : 08:33:21

Thanks Charlie
Certainly this solves my problem.
I just want to know is it possible to access memory table through some index of rows?
Similarly like Arrays in c which can be accessed by specifying row no.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 10:44:13

You can use table variables to get an analogue of an array like this...

give it a try, there is no mess with table variables.


DECLARE @myPens TABLE (
	[Id] INT IDENTITY(1,1) PRIMARY KEY
	, [pen] VARCHAR(50)
	)

INSERT INTO @myPens VALUES ('bic')
INSERT INTO @myPens VALUES ('parker')
INSERT INTO @myPens VALUES ('blueByro')
INSERT INTO @myPens VALUES ('brokenPieceOf****')
INSERT INTO @myPens VALUES ('brokenPieceOf****')

SELECT * FROM @myPens

UPDATE @myPens SET [pen] = 'fixedPen' WHERE [Id] = 4

SELECT * FROM @myPens

So if you had 2 pens with the same name they would have different ID's which would be analogous to being 2 items in an array.

And if you needed to you could use a INT counter and a while loop if you wanted to loop through the table.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-07 : 10:47:15

1 more thing....

Specifying a primary key is the only way to add an index to a table variable because setting a PK gives the table a clustered index.

Check out http://www.odetocode.com/articles/365.aspx for a nice introduction.

-------------
Charlie

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-07 : 11:08:47

moved from T-SQL 2000 forum

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources