FYI,

I figured out lots of things in my code.

I have created proper indexes in my tables which has reduced the execution time.

BTW,Isn't there a way to delete own...

Sorry for delay.

I have attached two files. One has schema of all the tables used

& second one contains the SQL query which is body of sproc

Its really long code .. approx 400 lines.

I was hoping to get some tips & tricks .. best practices when using temp tables in sproc

Yes. That's exactly what I am want to do.

But I don't know if its possible..

btw, I used cursor to do the work. But if its possible with SSIS, I would...

Sorry to bother u again... but i didn't get it clearly.

Can you explain it a bit more ...

I assume it has to start with creating user variable...using it in ConnectionString...

Ok... so, I assume once I have datareader rights .. I shd be able to do select from..

Correct?

I got it.

using sys.columns... precision & scale... it can be determined.

select

object_name(c.object_id) "Table Name"

,c.name "Column Name"

,s.name "Column Type"

,c.precision

,c.scale

from sys.columns c

join sys.systypes s

on (s.xtype = c.system_type_id)

where

object_name(c.object_id) in...

Thanks!

I figured it out.

Using substring & concatenating them did the trick for me.

Thanks to both of you for your inputs..

ok. gotcha!

Thanks for quick reply.

My guess was right....

Btw, is it possible to test this behavior? I couldn't figure how to do it. 🙁

I created a excel sheet with such names.....

All servers are SQL server 2005.

Data set isn't large.. 18000 rows approx.

I didn't get this:

Thanks to both of you. Through your responses, i was able to do it. 🙂

yeah.

Mostly, its addition. Deletions are very rare. well... i can't recall any delete actions. Actually, we don't remove codes which are not in use.. they are just kept...

no, the lookup values aren't totally replaced each year. Rather, they stay pretty much the same. There can be an addition or deletion from the table.

I think you are correct. I can add unique contraint on main table for columns forming composite PK and create an identity column as PK. Same way, I can work...

Thanks for your time and effort. This explains your idea and makes sense to me.

However, I think my problem is bit different. My main table has composite primary key. Though,...