Query Help.. - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Query Help..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-21 : 02:42:20

Hi..!!

I have 2 tables i want the query which returns me following records.
Table 1
A B
1 A
2 B
3 C

Table 2
A
1
2
3

Result
A B C
1 A 4
2 B 5
3 C 6

Column C is nothing but the maximum of the Col A of Table 2 and its incremented by 1

this can be done by cursor .. but i dont want to write cursor for this..

Any help ..

Thanks.

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 03:00:57

See point 1 helps you
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-21 : 03:35:48

well I got stuck over here..

Declare @ID int 

Select @ID = MAx(A) From TableB

Select A,B,IDENTITY(int,@ID,1) As PosID C
Into #Temp From TableA 

Select * From #Temp
Drop Table #Temp

This Code gives me error like this ..
Incorrect syntax near '@ID'.

Any clues..

Cant i specify any variable in Identity Function.. ????

Thanks.

Sucess Comes to those who Believe in Beauty of their Dream..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 04:43:22

no you can't.
you can do this:


use northwind
go
create table #temp (id int IDENTITY(1,1), employeeid int, customerid char(5))
go
declare @start int
set @start = 30
DBCC CHECKIDENT(#Temp, RESEED, @start)
go
insert Into #Temp (employeeid, customerid )
Select top 10 employeeid, customerid From orders
go
select * from #Temp

go

drop table #temp

Go with the flow & have fun! Else fight the flow

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-21 : 05:31:16

aha.. thanks a lot..

I will check this out.. but for the time being i accomplised by dynamic sql..

Declare @ID int

Select @ID = MAx(A) From TableB

Exec('Select A,B,IDENTITY(int,'+ @ID+ ',1) As PosID C
Into #Temp From TableA ')

Select * From #Temp
Drop Table #Temp

Do you Think any performance related issues in both ????

Thanks.. !!

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 06:55:42

After

Exec('Select A,B,IDENTITY(int,'+ @ID+ ',1) As PosID C
Into #Temp From TableA ')

Are you able to get correct result when you this?
Select * From #Temp

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-21 : 07:55:02

opps i used global temp tables.. not the local temp tables..

i used ##Temp..

and ya the result gave was perfect for me..

you can check out this code..

Create Table ##Tmp
(
A int,
b Varchar(10)
)

Create Table ##Tmp1
(
A int
)

Insert Into ##Tmp
Select 1,'A' union All
Select 2,'B' union All
Select 3,'C' union All
Select 4,'D'

Insert Into ##Tmp1
Select 1 union All
Select 2 union All
Select 3

Declare @ID varchar(1)
Select @ID =Max(A)+1 From ##Tmp1
print @ID
Declare @Var Varchar(100)
Set @Var = 'Select A,B, Identity(int,'+@ID+',1) As PosID Into ##Tmp3 From ##Tmp'
print @var
Exec(@var)
--Exec('Select A,B, Identity(int,' + @ID + '1) Into #Tmp3 From #Tmp')

Select * From ##Tmp3

Drop Table ##Tmp1
Drop Table ##Tmp
Drop Table ##Tmp3

This is just the test code..

Sucess Comes to those who Believe in Beauty of their Dream..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 08:07:04

But It wont work if you use Local temp table as is your previous example

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-21 : 08:12:36

quote:
Originally posted by madhivanan

But It wont work if you use Local temp table as is your previous example

Madhivanan

Failing to plan is Planning to fail

yeah it didnt worked when i was trying out so i have to use global tables.

Sucess Comes to those who Believe in Beauty of their Dream..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources