.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

creating temp tables!

Posted By:      Posted Date: September 17, 2010    Points: 0   Category :Sql Server
hi friends, 1. Is the tempdb in teh SQL server (2005) is a global database that is shared among other user defined databases? 2. Is is possible to create User defined temp databases like tempdb and how? i have create a temp table create table #usr_with_rls (idx int primary key, usr_name nvarchar(50), f_name nvarchar(50), l_name nvarchar(50), desig nvarchar(50)) and it was created in the tempdb database.  but the sp that creates the this table is in another database, therefore 3. How do i create this temp table "urs_with_rls" in my datbase and not in the tempdb? 4. I need to remove this temp datanase "urs_with_rls" from the server, onces teh sp completes it execution, how do i do that? thanks    

View Complete Post

More Related Resource Links

Data Points: Creating Audit Tables, Invoking COM Objects, and More


Dealing with error handling between T-SQL and a calling application, evaluating when a field's value has changed, and creating auditing tables in SQL ServerT are all common issues that developers must tackle.

John Papa

MSDN Magazine April 2004

Creating a user limited to a few selected tables.

Hi there!   Im facing some troubles setting up a SQL user for my system. A need to create an SQL User, whom only have read access from the view A, read/write to table B and should not be able to see the remaining tables. I have spent some time on this trying to figure out how the permission system actually works, but I havent found the configuration that actually works. What I have done is created a user without any schemas and roles and under securables granted access to read in A and read/write in B. When I use this configuration I cannot access any tables. If I give the user db_datareader/db_datawriter I can perform all actions on all tables.   Can somebody guide me through how to create a user with these limitations? I'm really stuck.   Thanks in advance, Stig Jørgensen

basic rules for creating relational tables


I'm still new to SQL. Is it a general rule amongst programmers to make sure every table has a primary key?


use temp tables instead of CTE's


I need to re write this query with CTE's to reflect differences in DNC ($$) between my @mindt and @maxdt

do I need to show a DNC @ MIN Date and DNC @ Max DT so i can calculate the differerence...can you help?

declare @mindt datetime
declare @maxdt datetime

set @maxdt = getdate()
set @mindt = getdate()-14;

With itmlvl as (



select RB.RBT_TYP_ID
	, rb.DSCR, rp.AMT
	, splr.EM_ITEM_NUM

from geprs_dnc..t_rebate rb 

Staging vs Temp tables in SSIS

Hi SSIS Guru,

Currently, we have our own internal app to pull data from the internet nightly to serverA. On serverA, there is a huge stored precedure doing update, insert and delete by joining lots of tables on serverB via synonyms over LINKED SERVER. However, there are only 3-4 tables on serverA used to join tables on serverB.

I am rewritting this process to eliminate linked server and use SSIS instead of stored procedure. What is the best option to bring tables from serverA to serverB? Should I create staging tables on serverB and refresh tables with TRUNCATE or use temp table instead?

Thanks much,
Staging vs Temp tables in SSIS

temp tables!


hi friend,

assume i have created a temp table, #t1 and after completion of query execution in the sp i can delete it from db.

in multi user enviorement, each user who acess that sp creates a temp table, i know teml tables has a structre #_______________<number>  so when each user access this sp, does it create a seperate temp table for each call?



Could not list tables from a DB when creating new ole DB source or ADO NET Source



i'm trying to add new database source to an data flow task, but it fails when i'm trying to select DB table.

Error message:

Could not retrieve the table information for the connection manager '....\.....'.


Field token out of range. (Microsoft Visual Studio)



Only way to select a table is write a table name to an parameter "OpenRowset"... 


Before i have installed visual studio 2010 for .net development. Maybe this is an issue source?

Using SQL Server2008R2









How to take the scripts of the changed SPs/Views/Temp tables/Functions automatically?



  We have a huge Database with more than 100 tables , more than 150 SPs.We are 10 members and we will be creating/deleting/updating SPs , functions, views, tem tables  everyday. Actually at the end of the day, we have to send the  scripts of latest SPs,functions,views,Temp tables to the server. So far what we are doing is  we manually go to the SQL server Scripts Wizard and selecting one by one all the SPs,functions,TTables and views. Is there any quicker way to take the changed stuffs instead going manually?

Need Urgent Help in Creating a Dynamic Textboxes according to database Tables


I need some help very urgent. I need create a table with Textboxes dynamically according to the database table.
Example: we have different articles in a database like cars, jewelery, boats. The data related to these articles is different in the tables. I have to show that data in a textboxes and let the user change those data and update. I can do the update and reading the data from database. But the problems is for each item the no.of columns and rows will differ.

My task is I need to get the data according to the use selection in the table with textboxes. I dont know which container (gridview, formview or etc) will suites for my requirement. please send me any sample code or any tutorial related to this. I am in real need guys.. I really appriciate your help.

Note: showing the data in a table should be a constraint. Because I should show data row wise (each row contains 3 textboxes and 3 labels (DB table column names))

What is the difference performance wise in creating a temp table using declare @table table or creat



I have a query that is taking forever to run on the production server.

Do you see a reason why will it take forever to insert it to a temp table... If i put my query in a cte it works fine...

This procedure was using a table variable and i changed it to a temp table but it didnt help performance wise

I found out that when i just do the select statement it works the same amount of time that cte takes but when it inserts it a temp table (around 75 rows) it takes forever.

Any help will be appreciated.



Better performance when all temp tables are at start of proc - STILL DON'T SEE WHY


I've already posted on this:


and was convinced even more by what was written in:



BUT when I actually tested a simple script with a profiler there was no difference in the number of deferred compiles (which is two) when all tables were at the start and when tables were spread in the procedure (According to the theory the second stored proc below should have produced only one deferred compile). This script completely contradicts the technical page link above. How can it be explained?




Can I have 3 different tables in a report? And, can I pass 3 temp table to SSRS as source for these

I'm using BI 2008 and sql 2005 and 2008.  Is there an example to show me?  Thanks.

Creating your SharePoint Governance Plan

One of the most common questions that I get from prospects as I'm talking to them about the creation of a governance plan and process is what does it look like. In other words, there are materials available which describe what should be in a governance plan but there isn't a ton of guidance on what the process of creating a governance plan and process are.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Creating Paging for a Repeater Control

A common question is "How do I implement paging within a Repeater?" Although the beauty of the Repeater control is its flexibility, you're on your own for building most functions. ASP.NET 2.0 has included some new controls that provide paging, as does the DataGrid in ASP.NET 1.1. However, this tip shows you how to roll your own paging for a simple data viewer.

Creating an AJAX Repeater in ASP.NET MVC

The "TaskList" is a tried an true example of the most basic thing that you can do in any framework. Unfortunately, I had some problems finding a really good example of all the steps necessary to get a task list to work in ASP.NET MVC 1.0.

Creating a Generic Entity Framework 4.0 Repository

With .NET 4.0 right around the corner, I thought it would be cool to download Visual Studio 2010 beta 2 and start playing around with the next release of Entity Framework.

The initial release of Entity Framework came with a great deal of criticism. To make matters worse, there was a large uproar when it was rumored that Microsoft would be abandoning LINQ to SQL, in favor of Entity Framework. This was because, at the time, many developers felt Entity Framework was an inferior technology to LINQ to SQL. To set things right, Microsoft proactively moved forward with improving Entity Framework, in time for the 4.0 release of the .NET Framework. This is good news because my initial impressions, so far, have been nothing but positive.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend