Deleting # NOT ##temp Tables

Apr 9, 2006

Hi how can I delete a local temp table?
I know a golbal is deleted by
if exists(select * from tempdb..sysobjects where name='##MyTemGlobalTable' and type='U')drop table ##MyTemGlobalTable'

but how do I drop a #MyTemGlobalTable' ?

thank you

View 8 Replies


ADVERTISEMENT

Deleting The Master Table Withour Deleting The Child Tables

Aug 9, 2007

Hi
i have to delete the master table data without deleting the child table records,is there any solution for this,  parent table has relation with the child table.
regards
vinod.t.v

View 9 Replies View Related

A Curious Error Message, Local Temp Vs. Global Temp Tables?!?!?

Nov 17, 2004

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?

View 2 Replies View Related

Stored Procedure Not Deleting The Temp Table

Mar 14, 2008

Hi
I have a search page having four fields. Giving any one of the field as input should retrieve search results in Gridview.
In GridView1 i have child Gridview for displaying details related to Gridview 1.

now i have to write storedprocedure for getting values in two grids.
there is one matching column in two tables i.e CNo.from first select statement we have to capture CNo and basing on that retrieve second table values.

I have a stored procedure for that but my problem is
i stored CNo in temp table i.e @MyTable .after doing select statements from two tables
i want to delete @MyTable. But iam not able to. so pls help me

My stored procedure code is here:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN

DECLARE @MyTable table (CNo varchar(255))



INSERT @MyTable

Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))



--Now do your two selects

SELECT *

FROM customer c

INNER JOIN @MyTable T ON c.CNo = T.CNo


Select *

From refunds r

INNER JOIN @MyTable t ON r.CNo = t.CNo


END

The output of storedprocedure is like this:

Iam getting all the columns of two tables but the CNo column is repeating twice in both the tables.
so please some one help me.
The CNo colum shouldnot repeat.

Thankyou

View 4 Replies View Related

Temp Tables Vs Temp Variables

Jul 20, 2005

I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet

View 2 Replies View Related

Temp. Tables / Variables / Process Keyed Tables ?

Feb 22, 2008

I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?

[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)

View 1 Replies View Related

Deleting From 2 Tables?

Jul 21, 2005

Hi, I am pretty new to SQL and know that I am probably going around this the wrong way.

I want to make a stored proc that deletes rows from table 1 and delete rows from table 2 where the common link is the id.

Any help would be greatly appreciated!

Many thanks

moop

View 5 Replies View Related

Deleting Tables

Feb 21, 2001

hi,
How can I delete two tables with a WHERE condition.
That is I can't do this -What can be an alternate of this statement -

Delete from Tab1,Tab2,Tab3
where
Tab1.name = Tab2.Name and
Tab2.name = Tab3.name

TIA

View 1 Replies View Related

Deleting Tables

Aug 24, 2005

Does anyone know why it takes a long time (Approx 10 minutes) to delete a DB. I have 2 identical environments for test and prod. I can delete DBs from my test environment quickly but it takes forever to delete a DB from the production environment. I am running SQL2000 SP3a. There are about 25 DBs in the environment with their sized ranging from 100meg to 2 gig. The only differnece in the environments is all test DBs are simple recovery mode and modt prod DBs are full.

Thanks,
Ken

View 5 Replies View Related

Deleting From 3 Tables

Apr 22, 2008

Hello everyone,

I have to delete some rows from three different tables. My tables are:

- VERSION_1
- ID


- VERSION_2
- ID_VERSION FOREIGN KEY


- VERSION_3
- ID_VERSION FOREIGN KEY

The 3 tables are related to each other by the VERSION_1 ID.

what i have to do is deleting first from VERSION_3, then from Version_2 and finally from VERSION_1. This is what i got so far:

DELETE FROM VERSION_3, VERSION_2, VERSION_1 WHERE VERSION_3.ID_VERSION = ? AND VERSION_2.ID_VERSION = ? AND VERSION_1.ID = ?

I cannot make it work, could someone please help me? i am stuck at this.

Thanks in advance

View 3 Replies View Related

Deleting From Several Tables

Aug 12, 2005

How do I delete from multi tables?
There are no issues with keys.

Will this work?
DELETE MC.Document
FROM dbo.dw_MasterClaim MC, dbo.dw_MasterClaim_Checks CHK, dbo.dw_MasterClaim_ChgDate,dbo.dw_MasterClaim_Diagnosis, dbo.dw_MasterClaim_InsNo, dbo.dw_MasterClaim_Pay, dbo.dw_MasterClaim_ProcLine, dbo.dw_MasterClaim_ProcLine2, dbo.dw_MasterClaim_ProcLine3, dbo.dw_MasterClaim_RiskPool, dbo.dw_MasterClaim_SuspHist
WHERE MC.Document IN
(Select Document
FROM fl1stDwImport.dbo.raw_MasterClaim
WHERE Document = MC.Document)

View 5 Replies View Related

Deleting Tables

Mar 25, 2008

I am new to SQL server and have just installed SQL Server 2005 (came withOffice 2007- think it's Express edition).I am trying to get my head around this.Just been working on an Access data base and have upsized to SQL.Having made several changes I have done this a number of times - linkingboth direct to file and creating a new application Access SQL project.Now I would like to know how to delete any tables or databases I have placedon the SQL before my system gets too messy.Furthur information - This is on my personal computer and is not shared orused by other people.Any ideas?Scott

View 1 Replies View Related

Deleting From Three Tables

Sep 13, 2007

I'm triyng to delete from three tables.

Issue Table:









Column Name
Data Type
Allow Nulls

issueID
int
Unchecked

name
varchar(50)
Unchecked

title
varchar(100)
Checked

description
varchar(500)
Checked

crntIssue
bit
Checked

frntPage
int
Checked

archived
bit
Checked

navOrder
int
Checked

dateCreate
datetime
Unchecked

Outlook Table:








Column Name
Data Type
Allow Nulls

ID
int
Unchecked

menu
bit
Checked

mnuOrder
int
Checked

mnuLevel
int
Checked

parent
int
Checked

issueID
int
Unchecked

masterPage
varchar(100)
Unchecked

visible
bit
Checked

name
varchar(50)
Checked

title
varchar(250)
Unchecked

description
varchar(1000)
Checked

summary
text
Checked

contents
text
Checked

image
varchar(50)
Checked

imgCaption
varchar(1000)
Checked

approve
bit
Checked

createDate
datetime
Unchecked


Links Table:








Column Name
Data Type
Allow Nulls

lnkID
int
Unchecked

linkFromID
int
Unchecked

linkToID
int
Unchecked


Issue table contais all the magazine issues. Outlook table contains all the pages of the Issue table. And the Links table contains the links or connection between parent page and child page. So here's what I wanted to do. When I click the delete issue button, I want to delete any pages, links, and issue from three tables that matches the issue ID that I wanted to delete. So for example, if I wanted to delete issueID 2, all the pages in the Outlook table and any links of those pages(lnkFromID) that are in the Links table should be deleted too. The lnkFromID and lnkToID are foriegn key of Outlook.ID table. The lnkFromID is the parent and lnkToID is the child.

I was wondering that maybe I can three delete statements for three tables. Is this a possibility? So any help is much appreciated.

View 1 Replies View Related

Deleting Rows From Many Tables

May 19, 2006

What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>"
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID"
SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)">
<DeleteParameters>
<asp:Parameter Name="UserID" Type="Int32" />
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID
DELETE FROM [photo] WHERE [UserID] = @UserID;
DELETE FROM [album] WHERE [UserID] = @UserID;
DELETE FROM [comment] WHERE [UserID] = @UserID;
...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!

View 8 Replies View Related

Automatically Deleting Tables

Aug 2, 2000

Is there a way to automatically delete tables in a database? I want to delete tables with a specific prefix in their names (that I assign to them) based on their age. Is there a way to do this automatically while keeping the tables that I don't want to touch?

joe

View 3 Replies View Related

Deleting Duplicates From Around 25 Tables

Jan 6, 2014

I need to remove duplicate data from around 25 tables. I want to use a while loop to go through all tables. If I list out all of the column names the query runs fine, but since there are 25 tables some with 50 plus columns I was hoping to use something like the following, which errors out because my sub queries return more than one result.

SELECT q.* from
(Select ROW_NUMBER() OVER ( Partition BY (SELECT [name] AS [Name] FROM syscolumns
WHERE id = (SELECT id FROM sysobjects
WHERE type = 'U'
AND [Name] = 'Orders')
)
Order by (select top 1 [name] AS [Name] FROM syscolumns

[Code] ....

View 9 Replies View Related

Deleting Records From 2 Tables At The Same Time

May 29, 2008

Hello all,
I have a DTS package set up to import a text file on a daily basis. I need to dump the data in 2 table after 7 days of the  last import .this is the code that I have
Delete From TblTemp
date(Day(-7), CurrentStamp).
But for some reason it deleting the data right after it imports it. And it doesn't delete anything out of the other table.
 
Thanks in advance

View 2 Replies View Related

Ran Out Of Ideas. Please Help In Deleting From Multiple Tables.

Apr 19, 2005

want to delete rows from two tables after a join.

in Access, here's something we can do:

delete table_A.*, table_B.*
from table_A left join table_B on ...


but in ms sql, it appears (to me) that you can only delete from one table at a time. how would i accomplish what i want to do?

also, is there a distinctrow equivalent in ms sql server? thanks

View 3 Replies View Related

Deleting Empty Tables In Sql Database??

Apr 2, 2006

Hello I have 16000 tables in a sql database and I need a sql query command to delete empty tables from that sql database please help.

F16 LÃ?GHTÃ?NÃ?NNNG

View 8 Replies View Related

Deleting Frm Multiple Tables At A Time

Jan 10, 2007

I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user.
DELETE FROM T1,T2,T3 WHERE column1='1'
how do i do it.

View 4 Replies View Related

Prevent User From Deleting Tables

Dec 12, 2007

Hi,

How can I prevent a colleage to delete tables in a specific database.
Yes he has access to Enterprise Manager. We would like to allow him read only to the live databases.

Is this possible?

View 4 Replies View Related

Resetting / Deleting All The Users In Aspnet_* Tables

Aug 9, 2007

Hi,How can i reset to zero, deleting all the users who are in the aspnet_* tables in my production 2003 server?Thanks

View 6 Replies View Related

Updating/Deleting Multiple Tables Simultaneously

Mar 1, 2005

Hi,

I'm using ASP with a JScript variant and MSSQL Server 2000. I would like to write a script that basically erases all data except for a few things.

Is there a way to update multiple tables at once without having to write lines and lines of code? I tried UPDATE tbl1,tbl2 SET uid='asc', but to no avail. It gave me a syntax error. My thinking behind it is something like... UPDATE dbo.* SET uid='mferguson' and after that I can delete stuff like DELETE dbo.*... Any ideas?

I know the above is ASP, I've tried this thread in the ASP forum with no avail... they referred me to this forum.

View 1 Replies View Related

Deleting Duplicate Records From Lots Of Tables

Aug 29, 2006

Hi All,

So.. I'm a complete newb to SQL stuff.

I managed to find the 'Deleting Duplicate Records' from SQLTeam.com (thanks, by the way!!).. I managed to modify it for one of my tables (one of 14).


-- Add a new column

Alter table dbo.tblMyDocsSize add NewPK int NULL
go

-- populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update dbo.tblMyDocsSize
SET @intCounter = NewPK = @intCounter + 1

-- ID the records to delete and get one primary key value also
-- We'll delete all but this primary key
select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK)
into #dupes
from dbo.tblMyDocsSize
group by strComputer, strATUUser
having count(*) > 1
order by count(*) desc, strComputer, strATUUser

-- delete dupes except one Primary key for each dup record
deletedbo.tblMyDocsSize
fromdbo.tblMyDocsSize a join #dupes d
ond.strComputer = a.strComputer
andd.strATUUser = a.strATUUser
wherea.NewPK not in (select PKtoKeep from #dupes)

-- remove the NewPK column
ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK
go

drop table #dupes


Now that I've got that figured out, I need to write the same thing to fix the other 13 tables (with different column info)- and I'll need to run this daily.

Basically I've put together some vbscript that gathers inventory data and drops it into an MSDE db (sorry - goin for 'free' stuff right now). Problem is it has to run daily so that I'm sure to capture computers that turned on at different times etc which ever-increases my database 'till I bounce off the 2GB limit of MSDE.

So the question is, what would be the best way to do this? Can I put the code into a stored procedure that I can execute each day?


Thanks for your help....

View 4 Replies View Related

Deleting Rows From Multiple Tables On A Condition

Oct 10, 2007



Hi,
I have different tables with the same schema as follows

ID Name
-----------------




<Table 1>

ID Name
-------------------
1 Name1
2 Name2
3 Name3


<Table 2>

ID Name
-------------------
1 Name1
4 Name4
5 Name5

I just want to delete the row where ID = 1 from these tables in one query ? Is it possible??

Thanks
~Mohan

View 6 Replies View Related

Deleting Records From Multiple Tables In SQL Server

Jul 13, 2007

I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.



I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):



Table 1: Classes --Columns: ClassID, ClassName

Table 2: Roster--Columns: ClassID, StudentID, Student Name

Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName

Table 4: Scores--StudentID, AssignmentID, Score



What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.



What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.



The stored procedure I created was



DELETE FROM Classes WHERE ClassID=@classid



I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:



The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.



What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?

I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.



Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.



Further, is there something else I need to do besides assigning primary keys and foreign keys?







View 6 Replies View Related

Deleting Multiple Tables Through The Management Studio GUI

May 26, 2007

is there a way to delete multiple tables using the Management Studio GUI?

View 1 Replies View Related

DTS - SP And Temp Tables

Nov 3, 2000

I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get
'Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Invalid Object name "#tempT1"'

Is there a way to make a DTS package call a stored procedure that uses temp tables?

Thanks.

View 2 Replies View Related

Temp Tables

Jun 12, 2002

Hi,

I want to check to see if a temporary table exists before I try creating one but I can't seem to find which sys table or schema collection I check. Any ideas?

Seoras

View 2 Replies View Related

##Temp Tables

Jun 16, 2004

I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.


CREATE PROCEDURE usp_CreateTakeoff
@iEstimate int,
AS

CREATE TABLE ##Temp_Takeoff
(
Field1 ......
Field2 ......
)

-- Add Structural data
usp_AddStructural @iEstimateID, 1, 'Structural'
usp_AddForming @iEstimateID, 2, 'Forming'
...
...
...
GO


Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".

I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.

Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.

Any ideas?

Mike B

View 4 Replies View Related

Temp Tables And UDF's

Dec 14, 2004

Hey,

I am in the process of modifying some stored procedures that currently do not use temp tables. For this modification I am required to make the stored procedures use temp tables. There are several UDF's within this stored procedure that will need to use the temp tables, and this is where in lies the problem. Does anyone know of a work around that would allow UDF's to use temp tables, or does anyone know of alternate methods instead of temp tables that wouldn't involve too much change?

Thanks

View 1 Replies View Related

Temp Tables

Apr 7, 2006

Hi,

I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting
invalid object errors on the temporary tables:
Invalid object name '#temp_table1'

The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.

Thanks,
Jeff

View 6 Replies View Related

Temp Tables

Sep 4, 2007

hi All,
I am using a temp table creating it as

create table #process
(
tons of coomuns in here
)
then
insert into #process(collumns)
select from peon
where etc....

Can i use the same temp table definition , but insert into another tempTable.Does alias help me accomplish this task.
Thanks for your input

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved