I want to use one stored procedure to delete rows in three different tables - how do I write this for Sql Server 2000 please?
PROCEDURE dbo.DeleteSubmission
@C_ID int
AS
DELETE *
FROM tblCompanies
WHERE C_ID = @C_ID
DELETE *
FROM tblStores
WHERE CS_ID = @C_ID
DELETE *
FROM tblDistributors
WHERE CD_ID = @C_ID
RETURN
Hello everybody,
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Is it possible? Please advice.
Thanks in advance.
Regards,
Ashish
Just wondering if this is good form:Alter Procedure "mySPName"@UniqueID intASset nocount onset xact_abort offDELETE FROM tblNameOneWHERE(tblNameOne.UniqueID = @UniqueID)DELETE FROM tblNameTwoWHERE(tblNameTwo.UniqueID = @UniqueID)Is it a good idea to run multiple detele statements within one SP?thanks,lq
View 5 Replies View Relatedwill my fetches on a cursor continue seamlessly even if deletes and multiple commits are occuring on the underlying source table? I want to make sure the cursor will not lose it's place as I believe some older dbms's did.
View 11 Replies View Relatedso, I am trying to write an sql that requires information across two
different database. It's under the same sql server. However, the
location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
Thanks in advance, and merry X'mas.
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I have to do following scenario,
if 1st query Then 2nd Query
Else 'Msg'
How Can i do this using Case Statement??how can do this by Other way??
CREATE TABLE [dbo].[multiple](
[emp_code] [varchar](20) NULL,
[swip_in] [nvarchar](255) NULL,
[swipe_in_date] [nvarchar](255) NULL,
[swipe_out] [nvarchar](255) NULL,
[Swipe_out_date] [nvarchar](255) NULL
[Code] ....
I want result like below:
Empcode swip_in1, swip_out1, swip_in2, swip_out2, swip_in3, swip_out3
Condition :
1. 24 hrs who are multiple time punched
I have several database files, all in the same directory. I want to write SQL statements that access two databases at once. For example, if only one database would be involved, I would write:
SELECT Test_Source_ID_column, Data_column
INTO Test_Destination_table
FROM Test_Sources_table
The above query copies two columns (Test_Source_ID_column and Data_column) from Test_Sources_table into a brand new table called Test_Destination_table.
Now the above query is fine for both the old and new tables being in the same database. But what if Test_Sources_table is in file Old.dbf and I want the new table Test_Destination_table to be in New.dbf?
Both Old.dbf and New.dbf are in the same directory. Therefore, there is no security issue about using ......etcpasswords. I should therefore be able to use relative paths. So, I also want to know how I can do that select on two databases in the same directory without typing long, long paths.
Is there a way to do queries that access multiple database files (all database files being in the exact same directory) AND use relative paths or no paths (and certainly not type a full path)?
NOTE 1: I'm using SQLEXPRESS, therefore do not have the SQL Server 2005 tools. I have Visual Studio 2005 Developer Edition with its tools.
NOTE 2: One table is accessed through ODBC. The other table is accessed through SQLEXPRESS.
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order.
The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table.
Any ideas would be greatly appreciated.
thanks
John
I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).
I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.
Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.
I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:
;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID
[Code] ....
I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?
By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?
Hi,
We are developing a new oltp application using SQL Server 2000 and are debating whether to use "cascade delets" or not. In earlier apps, we had issues using cascade deletes like someone deleted parent by mistake and all child records got deleted OR SQL Server crashed in middle of cascade delete and records were lost or performance became an issue when there were huge # of child records to be deleted,etc.
Are there any recommendations for/against using Cascade deletes and when to use and when NOT to use cascade deletes ?
Thanks
Satish
I need some suggestions concerning the issue cascading deletes in a self-referencing table, i.e. a table with a foreign key pointing at the primary key in itself. Although SQL Server still does not support cascading deletes the declarative way there are some other ways to handle this situation. One common way is to use trigger coding with the simple structure
CREATE TRIGGER DelCascadeTrig
ON self_ref_tab
FOR DELETE
AS
DECLARE @C_FK xxxxxx
SELECT @C_FK = C_FK FROM DELETED
BEGIN
DELETE self_ref_tab
FROM self_ref_tab, deleted
WHERE self-_ref_tabC_FK = deleted.C_PK
END
where C_PK is the primary key column and C_FK the foreign key column. The problem is that this simple pattern does not work with self-referencing tables, because the removal of dependent rows deeper layer (n-2, n-3 etc. if the originating delete request is level n and the first level of dependant deletes handled by the trigger code is n-1) would require the delete trigger to fire more than once for the same delete operation. As far as I know, in ver. 6.x triggers are executed only once per SQL statement and in this case the n-2, n-3 etc. level rows would have been left as "orphans".
In ver. 7.0 I suppose this should work fine because of the new recursive trigger execution possibility (trigger will fire up to 32 recursive times per SQL statement), but in the meanwhile (i.e. my case util we have upgraded all our servers) the delete logic for a cascading, self-referencing relationship must be handled completely within one execution of the trigger.
My question is now: does anyone know anything about any common algorithm or trigger code example solving this problem.
Thanks in advance
Bjorn Ehnberg
When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
thanks
dog
I'm wondering if anyone has compared the speed of different kinds of deletes.
Assuming super large tables, I wonder, what is the speed ordering for the following operations:
* Remove a column
* Remove a row
* Remove a table
I'll experiment to see if I can get numbers comparing the speeds, but, if someone's already done the work, I'd love to know
thanks
I haven't used cascading deletes in the past but we're starting a new database and it seems like a good way to go to keep data clean. Or at least it did seem like a good way until I ran some tests. I have 3 tables.
People PeopleEmails Email
(pk)peopleid (pk)peopleid (pk)emailid
fname (pk)emailid address
lname emailtype
password
In this structure, the peopleemails table is simply an association table between the email and people table. I have setup up relationships in a diagram so that when a person is deleted, it cascades to peopleemails and removes the entry there. I also had a cascade set up hoping that when an entry was deleted from peopleemails, it would remove it from the email table but this is not happening. The relationship between email and peopleemail is primary key table email.emailid and foreign key table peopleemail.emailid. Is there a way to get this to work to remove the email address if a peopleemail entry is removed? Thanks.
Hello,
I have three tables:
CREATE TABLE TASKS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
NAME VARCHAR(20) DEFAULT '' NOT NULL UNIQUE,
PRIMARY KEY (SEQNO)
);
CREATE TABLE JOBS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
NAME VARCHAR(20) DEFAULT '' NOT NULL UNIQUE,
PRIMARY KEY (SEQNO)
);
CREATE TABLE JOBTASKS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
JOBID INTEGER DEFAULT 0 NOT NULL REFERENCES JOBS(SEQNO),
TASKID INTEGER DEFAULT 0 NOT NULL REFERENCES TASKS(SEQNO),
PRIMARY KEY (SEQNO)
);
When I delete a record in JOBS I would like to create a trigger that would delete the related records in JOBTASKS.
This is my attempt; but it does not work.
CREATE TRIGGER JOBS_BEFORE_DELETE
ON JOBS
FOR DELETE
AS BEGIN
DELETE FROM JOBTASKS
WHERE JOBID = (SELECT SEQNO FROM DELETED);
END;
Thanks,
Steve
I'm hoping someone has seen this before because I have no idea what could be causing it.
I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.
My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.
The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[TestTrigger]ON [dbo].[items]AFTER INSERT
AS BEGIN
SET NOCOUNT ON;
INSERT INTO tblHistory(table_name, record_id, is_insert) VALUES ('items', 123, 1)
END
tblHistory's field types are (varchar(50), BigInt, bit).
As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?
Thanks in advance!
I have a logins table, a loginroles table (intermediate), and a roles table
When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005.
Any help would be appreciated.
Thank You,Jason
hello,
Can you give me an example of suppression under DOS, of the files with under-directories by exec cmdshell
thank you in advance
Pascal
It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in
View 4 Replies View RelatedI have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
View 2 Replies View RelatedHi
I keep data in four seperate locations, each with different data retention.
the problem is, i keep the publisher "thin" - only 24 hrs of data) the data in the relevant tables is about 250000 a day) and these deletes are blocked at the subscribers by commenting out the body of the repl proc.
this does however cause a burst in bandwith & locks the subscribers, even though no actual delete takes place.
does anyone know if you can actuaklly NOT REPLICATE delete commands for specified tables at all - so that the message of the delete wont even be SENT to the subscriber?
Des
Hello all.
Please help me shed light on the following problem:
I am deleting from 2 tables in a transaction. I experience a deadlock occasionally when deleting.
Here are the tables:
create table TABLE_1 (
ID integer not null,
TYPE_CD varchar(50) not null,
STATUS_ID integer not null,
NAME varchar(500) not null,
COMMENT varchar(500) null,
CREATED_DATE datetime not null,
CREATED_BY varchar(15) not null,
LASTMOD_DATE datetime null,
LASTMOD_BY varchar(15) null,
constraint PK_TABLE_1 primary key nonclustered (ID)
)
create table TABLE_2 (
TASK_ID integer not null,
TABLE_1_ID integer not null,
COMMENT varchar(500) null,
constraint PK_TABLE_2 primary key nonclustered (TASK_ID, TABLE_1_ID)
)
Table table_2 is an intersection table between table_1 and another lookup table (called task).
Whenever I delete from table_1 I need to delete from table_2 to maintain integrity.
The reason I do not have clustered indexes on the tables is b/c they start out empty (every day a housekeeping task deletes from them. Unfortunately I cannot change this...).
In my (web/jsp) application before I delete, I select from table_1 to get data I need to remember after the delete.
In order to avoid the "select for update problem", I use the "UPDLOCK" hint in the select like this:
select *
from table_1 WITH (UPDLOCK),
table_2 WITH (UPDLOCK)
where table_1.id = table_2.table_1_id "
and table_1.id = ?
and table_2.task_id = ?
I then issue my deletes in the following order:
delete from table_1
where task_id = ?
and table_1_id = ?
and then,
delete from table_1 where id = ?
The application sets the isolation level to READ COMMITTED.
The above 3 sql statements are executed as part of a transaction. Occasionally, when I drive the application, I get a deadlock. This happens intermittently. I have been able to reproduce the problem by attempting to delete records really fast, one after the other from the (web) front end.
When the deadlock occurs these are the locks that are held by the running processes:
Blocked SPID (10):
============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_1 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_1 KEY U GRANT Xact PK_TABLE_2 (a400f609034c)
app.dbo.TABLE_1 RID U GRANT Xact TABLE_2 1:157:3
app.dbo.TABLE_1 PAG IU GRANT Xact PK_TABLE_2 1:443
app.dbo.TABLE_1 PAG IU GRANT Xact TABLE_2 1:157
app.dbo.TABLE_2 KEY S WAIT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 KEY S GRANT Xact PK_TABLE_2 (ef007b1066ea)
app.dbo.TABLE_2 TAB IS GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IS GRANT Xact PK_TABLE_2 1:252
Blocking SPID (12):
=============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_2 KEY X GRANT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 RID X GRANT Xact TABLE_2 1:176:3
app.dbo.TABLE_2 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IX GRANT Xact PK_TABLE_2 1:252
app.dbo.TABLE_2 PAG IX GRANT Xact TABLE_2 1:176
I could not figure out what the problem is. Please help me with this.
Any help will be much appreciated.
Thanks in advance
Hello all. We are just getting started with replication and I'mwondering if there is a way to not have deletes replicated. I know Icould manually remove the delete trigger from every table but I'mintrested in have this a bit more automatic (like when its creating thesubscription).ThanksStephen
View 1 Replies View RelatedI have the following tableCREATE TABLE [tbl_Items]([item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__item_idPRIMARY KEY,[parent_id] int DEFAULT(NULL) CONSTRAINTFK_tbl_Items__item_id__parent_id REFERENCES [tbl_Items]( [item_id] ) ONDELETE NO ACTION ON UPDATE NO ACTION)My Intention was to create a table that when I delete a record, allrecords that have on the [parent_id] field the deleted record[item_id].I am trying to avoid having to use triggers or create a storedprocedure that firsts delete the children (recursively) and thendeletes the parent.Is there any way to do this by changing my table definition here?
View 1 Replies View RelatedHi, I'm using SQL server 2000, and I have set up two tables, table Aand table B. Table A and B have a foreign key constraint such that ifan entry is deleted in table A, then all the entries in table Bassociated with that entry are deleted as well. I imagine that I canfind out the number of records that are deleted in table A when Iexecute the SQL, but is there an easy way of determining the number ofrecords that will be deleted in table B?Thanks,Dan
View 1 Replies View RelatedHi guys,
A simple question of good practices: is it better to have a cascade update/delete on a table? Or is it better to do it "by hand" in a SP?
Thanks a lot.
I seem to be having a problem on all of my SQL servers. WHen I or a developer attmept to do a delete on a table i get a Log file for database is full. I truncate the log try again and get the same error. IT doesnt seem to matter how much is being deleted or how big the table is. THis is very strange and very frustrating.
Thanks
David
Microsoft article Q142480 states "Triggers cannot be used to perform cascading updates and deletes if ForeignKey-to-PrimaryKey relationships have been extablished using SQL Server's DRI."
Does this mean that I cannot declare FK's in my scripts if I want to have triggers in the table? Do I just add a column in my table that will have a foreign key in it, but just not reference it in my script? Can someone clarify for me?
Thanks,
Nathan
I was testing how the WITH CHECK OPTION works. I though that it prevents row from dissappearing from the view implementing this option but I found that I was able to DELETE all rows through the view - Why is this???
Please note that I only have the base version of sql server 2000 - No patches applied
Thanks for your help
------------------------------------------- Test
Create table T100 (A int)
GO
Create view VT100 AS (SELECT * FROM T100 WHERE A < 2) with check option
GO
INSERT INTO T100 VALUES (1) -- Part of the view's result
INSERT INTO T100 VALUES (2)
GO
SELECT * FROM VT100 -- SHOWS 1
GO
INSERT INTO VT100 VALUES (-2) -- Works
GO
UPDATE VT100 SET A=5 -- Update fails because of WITH CHECK OPTION - GOOD
GO
INSERT INTO VT100 VALUES (999) -- Inser fails because of WITH CHECK OPTION - GOOD
GO
DELETE FROM VT100 -- DELETES ALL ROWS!!!! ******************* ???
ekareem
Hi,
I've been having problems with my tempdb filling up, and causing all databases on the server to stop functioning properly. I've been removing alot of data lately (millions of rows), and I think this is the reason why my tempdb log is going thru an unusual load.
Whats the best way to make sure the tempdb doesnt fill up causing me major problems? I had temporarily turned off backups while I was having a new HD put in. Am I right in thinking that when a DB is backed up, the tempdb log is reduced in size? Should maintaining a daily backup solution help keep things under control ?
Thanks very much for any tips!
mike123
Please help.I have a table with single row. I need to allow only UPDATEs of thetable, forbid INSERTs and DELETEs. How to achieve it?Thank you for information/RAM/
View 3 Replies View Related