Table variables and identities

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

General SQL Server Forums

New to SQL Server Programming

Table variables and identities

Author

Topic

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-16 : 09:44:03

Can an identity be created in a table variable?

Can joins be performed between table variables to be inserted into another table variable?

Am I better of using a temporary table?

(I'm working in a stored procedure here)

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-16 : 09:48:30

1 Yes
2 Yes
3 It depends

Madhivanan

Failing to plan is Planning to fail

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-16 : 10:03:07

Thanks MAdhivanan,

What is the syntax for creating an identity in a table variable? Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-16 : 10:06:36

[code]
declare @tmp table (iden int identity (1,1), other varchar(100))

insert into @tmp values ('blah')

select * from @tmp
[/code]

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 10:08:00

[code]
SET NOCOUNT ON

DECLARE @MyTableVar TABLE
(
MyID int IDENTITY(1,1) NOT NULL,
MyOtherCol varchar(10),
PRIMARY KEY
(
MyID
)
)

INSERT INTO @MyTableVar (MyOtherCol) VALUES ('FOO')
INSERT INTO @MyTableVar (MyOtherCol) VALUES ('BAR')

SELECT * FROM @MyTableVar
[/code]