hi, i have one temporary table
#tmp_tbl
before i create a temporary table , i would like to drop it first, but i try
"drop table if exists #tmp_tbl "
it doesn't work , any help ? thanks in advance
I'm trying to drop a temporary table. I keep getting this error:
Cannot drop the table '#temp_table', because it does not exist in the system catalog.
I tried the following but it did not work, so help would be appreciated.
if object_id('tempdb..#temp_table') is not nulldrop table #temp_table
I created the #Temporary table in MS SQL. Now i want to drop this #Temporary table, but i want to check first before i drop the this table. How to check the Temporary table exist or not in MS SQL?
View 4 Replies View RelatedHi. Thru a sproc, I drop & re-create some temp tables.When I call that sproc from the client, though, I cannot drop thetables.I need to allow the user, say "Alex", to drop/create tables (actually,that would be DDL). Which role should "Alex" assume ? How do I do that?I run the following sproc named, say, "CREATE_TABLE" (SNIP):__________________________________________________ __________________Set @StrSQL = 'if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[' + @TableName + ']'') and OBJECTPROPERTY(id,N''IsUserTable'') = 1) drop table [dbo].[' + @TableName + ']'Exec (@StrSQL)Set @StrSQL = 'CREATE TABLE [dbo].[' + @TableName + '] ([GROUP] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,...........[Stuff] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]'Exec (@StrSQL)Set @StrSQL = 'GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+ @TableName + '] TO [Alex]'Exec (@StrSQL)__________________________________________________ __________________As you can see, it is dynamic, because I need to repeat it for many@TableName values - that means, further more, that I will be executingthis in the context of the current user, Alex, and therefore I have togive Alex rights to both executing the sproc and to the tables referredto by the sproc, as specified by @TableName.I created a _TEST sproc which contains only the following:_______________________________________________CREATE PROCEDURE _TEST ASDROP TABLE [dbo].[SomeTable]RETURN_______________________________________________When I execute it from the client, thru ADODB, on user Alex, I get"User does not have permission to execute this operation on tableSomeTable"The table has been created thru "CREATE_TABLE", abovePlease help, I have to finish this tomorrow, and I'm under tons ofpressure.Thanks a lot,Alex.
View 1 Replies View RelatedI used code below to drop one temporary table. How to make code to drop all temporary tables?
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
Just a quicky about temporarary tables. If using QA, when you create atemporary table, it gets dropped if you close the query. Otherwise youneed to state 'DROP TABLE myTable' so that you can re-run the querywithout the table being there.Sometimes, you can have quite lengthy SQL statements (in a series)with various drop table sections throughout the query. Ideally youwould put these all at the end, but sometimes you will need to dropsome part way through (for ease of reading and max temp tables etc...)However, what I was wondering is :Is there any way to quickly drop the temporary tables for the currentconnection without specifying all of the tables individually ? Whentesting/checking, you have to work your way through and run each droptable section individually. This can be time consuming, so beingnaturally lazy, is there a quick way of doing this ? When workingthrough the SQL, it's possible to do this quite a lot.ExampleSQL Statement with several parts, each uses a series of temporarytables to create a result set. At the end of a section, these worktables are no longer needed, so drop table commands are used. Thefinal result set brings back the combined results from each sectionand then drops those at the end.TIARyan
View 2 Replies View RelatedHi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedHi all!
I would like to gain data from a temporaly table created by an EXEC command.
e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right.
But I would like to use it:
SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow
WHERE...
I know that a function can return a table but an sp cannot.
How can I do it?
Thx: Gurmy
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedI have a table that includes the html-output of different parts of my pages. This table grows very big very fast, and rows older than 24 hours are useless.
My question is if it is possible to have temporary rows, whose are automatically deleted after these 24 hours? And then how to accomplish that?
Hi
View 10 Replies View RelatedI have used an access linked table to import data from a sharePoint list hosted on SharePoint 2007 server.
I hope someone else has done this. (1)I Does anyone know of any other way to pull data from a SharePoint 2007 list.
(2)Has anyone pushed data to a SharePoint List from a SQL Server table?
I tried doing that via the linked table but the destination task of the data flow task does not allow me to push data into the table.
(3) Has anyone designed an SSIS package to write data to an AccESS table from a SQL Server table?
Any help would be appreciated
I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
Tips, anyone?
Thanks!
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedIs it possible to insert data into a table from a temporary table that is inner join?
Can anyone share an example of a stored procedure that can do this?
Thanks,
xyz789
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies View RelatedBanti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures.
Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example:
StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1...
Or using a table-valued function to return a temp table as the result.
I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
Hi
I am using SQL 2000 I have the following code. When saving it, I am getting an error:
There is already an object named '##tbl' in the database
This is although #tbl is dropped.
Is threrea way to avoid this error? the only work around I found was to create a string with the SQL command and call EXEC, but I don't like this solution as it prevents early compilation of the procedure.
declare @x int
set @x=1
IF @x=0
begin
create table #tbl (
abc int
)
drop table #tbl
end
IF @x=1
begin
create table #tbl (
abc int
)
drop table #tbl
end
Than you.
Hi
I have to create a temporary table for generating a report in VB.
Pls help how to check the temporary table name in database.
I want if exits than drop and create a new one.
thanks
asm
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Sales | -20,000.00 | 01 | 2005
===================================================
1000| Sales | -10,000.00 | 02 | 2005
===================================================
1001| Cost | 5,000.00 | 01 | 2005
===================================================
1001| Cost | 5,000.00 | 02 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description | Period 01 | Period 02 | Year to Date
=========================================================
Sales | 20,000.00 | 10,000.00 | 30,000.00
Total Sales | 20,000.00 | 10,000.00 | 30,000.00
Cost | 5,000.00 | 5,000.00 | 10,000.00
Total Cost | 5,000.00 | 5,000.00 | 10,000.00
=========================================================
Profit | 15,000.00 | 5,000.00 | 20,000.00
The above report would list 4 columns, with the last column being a calculated field as a sum of
Period01 + Period02 Amount, sorted by GL_ID and group under a summation row called
Total Sales & Total Cost.There would be a net amount appearing as Profit (Total Sales-Total Cost).
Guys, hope someone out there can help me with the sql command for the above report?
How would someone set up a temporary table from a database in order to then run a query against it to get more accurate data??
View 11 Replies View RelatedHi,
i have:
[first table]
user_id
user_name
user_password
[second table]
set
item
order
Since the second table is somehow only temporary table (having at the front ajax script), i need the third table to store user's ID and all the info from second table.
What would be the easiest way to do it, since the first table contains only one row per user and the second one 40 rows per user.
thank you for your thought!
Hi,
my code looks like this
Declare @Temp Table(id_customer int, id_product varchar(10))
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC104')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC143')
Insert Into @Temp(id_customer, id_product) Values(12, 'ABC103')
Insert Into @Temp(id_customer, id_product) Values(13, 'ABC102')
Insert Into @Temp(id_customer, id_product) Values(14, 'ABC101')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC10')
Insert Into @Temp(id_customer, id_product) Values(15, 'AABC11')
select * from @Temp
Declare @results VarChar(100)
Select @results = IsNull(@results + '; ', '') + IsNull(id_product, '')
From @Temp
where id_customer = 12
Select @results as all_products
and when i run "Select @results as all_products" what shall i do to get id_customer along? if i do "select id_customer, @results as all_products" I get error in return :(
thank you
hi!
i used some temporary table in store procedure (sqlserver 2005)
our team have report software calisto .
the calisto use crystal and reports which use
this store procedure .
because of that,
we have list of many temporary table with the same name
#dbo.sug_name ... ,#dbo.sug_name ... ,......
in the system database .
what could be the reason for that and how can we drop it ?
Msg 3701, Level 11, State 5, Line 2
Cannot drop the table '#sug_name', because it does not exist or you do not have permission."
Hi all
Is it possible to name a table when a script runs with the date ie
select name, address
into test (date)
from company
where the (date) is will be todays date
Thanks
Richard
Hi
I call some SQL server procedures via OLEDB under Visual C++.
[ Commands like " EXEC myProc 'param1', 'param2' " ]
My connection string is Provider=SQLOLEDB;Data Source=.SQLEXPRESS;Integrated Security=SSPI;Initial File Name=C:devsrcindb.mdf
I encounter a pb when there is a 'SELECT (...) INTO #table' into my db procedure.
Something like:
ALTER PROCEDURE myProc
AS
SELECT 1 as 'return1', 2 as 'return2' into #table
SELECT * FROM #table
RETURN
works of course very well when I call it from Visual or Server Management Studio Express
but doesn't return any recordset when I call it from OLEDB.
2nd thing:
SELECT 1 as 'return1', 2 as 'return2' into table
SELECT * from table
-> table is created [I can see it in the db], but no selection is returned to my OLEDB caller.
Is it a security pb ?
Hi All,
DECLARE @MyTableVar table( EmpID nvarchar(10) )
select login_id from cpm into @MyTableVar
Above syntax is not working....
Actually i want to table variable and store the result returned by stored procedure.
How to do that...
Thanks and reagards
A
Hello once again,
My last question concerns (temporary) tables. In a web form (VWD Express, Asp,Net 2.0, VB, .mdf db, Ajax) I have two gridviews. One gets its data from textboxes and dropdownlists as the user clicks an ok button. This data is then sent to a "junk" table in the db. The other gridview retrieves this data "automatically" and displays the data, filtered and grouped by some SQL statements. When the user finally clicks an Insert record button, the data from this second gridview gets inserted in another table in the db and the data from the first "junk" table is disposed.
I guess it shouldn't be necessary to insert, retrieve, and delete the data in and from the "junk" table. Even though it does work like a charm, I guess it would be better if I could do it wit hsome kind of "temporary table", array or the like so that the db wouldn't need to be updated to this extent. Still, I really like the simplicity of using gridviews and the rest of the page's code is complex enough, so I don't really want to substitute this behaviour for tons of hand-written, hard-to-follow, and hard-to-integrate code.
What are my options? Any help would be greatly appreciated.
Pettrer
Im trying to find a table and Drop in T-SQL
using this script.
/* Start */
Use Students
IF exists (Select * from information_schema.tables
where table_name ='##Exams_result)
drop table ##Exams_result
go
Create table ##Exams_result..............etc
/* end */
But I cant find my temporary table on this way...
Any sugestion?
Luiz Lucasi
lc@culting.com
Rio de Janeiro - Brazil
Hi,
Can any of you tell me what is right?
When we are writing a big query that against to 25+ tables (it has lots of lookup and lots of business logic in it so it will have lots of left outer joins, right outer joins, inner join, UNION, group by, and lots of SQL server functions) should we put everything in one select statement? Or we should create lots of temporary tables and then join them together?
My friend told me that I should use one statement instead of lots of temp tables in order to reduce the physical read. He does not like the temp table idea since there will be lots of user running the query in the same time and it will use lots of space. But I don’t believe that is true. (I believe when you put everything in one statement, SQL server still needs to create the result set in the temp db.) There will be lots of join going on and it could hurt the performance.
I believe that
1.We should create the temp tables and then join the temp tables together later to get the final results. we can create the index on the temp table at runtime.
2.If the order of columns in the index does not match to our where clause, then the index is useless.
3.If there is a SQL function in the where clause, the index is useless. (for example, where Datepart(yy, orderdate)=’1998’)
4.If there is a NOT in the where clause, the index is useless.
What do you think? Are you agree with me?
Following is the query. Is there any better way to do this?
Select d.Description as Division,
c.Description as Region,
b.StateCode as State,
b.FacilityNumber,
b.Name as FacilityName,
(Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate,101)
Else 'Pending' End) as PriorStdSurveyExit,
(Case When a.ExitDate Is Not Null And a.IJValid > 0
Then Convert(Varchar(10),a.ExitDate+89,101)
Else '' End) as ThreeMonthWindow,
(Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+179,101)
When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+179,101)
Else 'Pending' End) as SixMonthWindow,
(Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+450,101)
When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+450,101)
Else 'Pending' End) as FifteenMonthWindow
From (
Select a.FacilityNumber, a.ExitDate, IsNull(b.FacilityNumber,0) as IJValid
From
(
Select b.FacilityNumber, Max(a.ExitDate) as ExitDate
From Survey a
Inner Join Facility b on (a.FacilityID = b.FacilityID)
Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID)
Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID)
Where a.VerbalResultDate is Not Null
And c.Description Like 'Standard%'
And d.Description Like 'LTC%'
And a.ExitDate <= @InDate
Group By b.FacilityNumber
) a Left Join
(
Select Distinct b.FacilityNumber
From Survey a
Inner Join Facility b on (a.FacilityID = b.FacilityID)
Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID)
Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID)
Inner Join ComplianceRatingCode e on (a.ComplianceRatingCodeID = e.ComplianceRatingCodeID)
Where a.VerbalResultDate is Not Null
And (c.Description Like 'Standard%'
And d.Description Like 'LTC%')
And e.Description Like 'Category 4%'
And a.ExitDate <= @InDate
Union
Select Distinct b.FacilityNumber
From (
Select b.FacilityNumber, Max(a.ExitDate) as ExitDate
From SurveyCitation e
Inner Join Survey a on (a.SurveyID = e.SurveyID)
Inner Join Facility b on (a.FacilityID = b.FacilityID)
Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID)
Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID)
Inner Join Citation f on (e.CitationID = f.CitationID)
Inner Join SSLetterCode g on (f.SSLetterCodeID = g.SSLetterCodeID)
Where a.VerbalResultDate is Not Null
And d.Description Like 'LTC%'
And a.ExitDate <= @InDate
And g.SSLetterCode in ('J','K','L')
Group By b.FacilityNumber
) a
Right Join
(
Select b.FacilityNumber, Max(a.ExitDate) as ExitDate
From SurveyCitation e
Inner Join Survey a on (a.SurveyID = e.SurveyID)
Inner Join Facility b on (a.FacilityID = b.FacilityID)
Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID)
Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID)
Where a.VerbalResultDate is Not Null
And c.Description Like 'Standard%'
And d.Description Like 'LTC%'
And a.ExitDate <= @InDate
Group By b.FacilityNumber
) b on (a.FacilityNumber = b.FacilityNumber)
Where a.ExitDate >= b.ExitDate
) b on (a.FacilityNumber = b.FacilityNumber)
) a Right Join Facility b on (a.FacilityNumber = b.FacilityNumber)
Left Join Region c on (b.RegionID = c.RegionID)
Left Join Division d on (c.DivisionID = d.DivisionID)
Order By d.Description, c.Description, b.FacilityNumber
hi, good day, i facing a problem
Code:
CREATE PROCEDURE [SP_TEST]
AS
SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER
-------------------------------------------------------------------------
SELECT DISTINCT
COUNT(CUST_CD)
FROM
#TMP_CUSTOMER
GO
when i run in query analyzer , using "exec SP_TEST" , it work and display result
but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
thanks for guidance