help with the script change
-
I need to change the script to delete partially rows from patient data instead of all the rows
DECLARE table_cursor CURSOR READ_ONLY FOR
SELECT name,
CASE name
WHEN 'visits' THEN 1
WHEN 'patients' THEN 2
ELSE 3
END AS sortkey
FROM sysobjects
WHERE xtype = 'U'
AND dbo.tabletype(name) IN ('patient data', 'transient', 'obsolete')
AND EXISTS (SELECT * FROM sysforeignkeys WHERE sysforeignkeys.rkeyid = sysobjects.id)
ORDER BY sortkey, name
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @tablename, @sortkey
WHILE @@fetch_status = 0
BEGIN
-- Delete the rows of this table.
SET @sql = 'DELETE FROM ' + @tablename
PRINT @sql
EXECUTE (@sql)
FETCH NEXT FROM table_cursor INTO @tablename, @sortkey
END
CLOSE table_cursor
DEALLOCATE table_cursor
Thanks in advance
-
What exactly are you trying to accomplish in this sub. And what lines are you trying to keep and in what table?
-
I think you need to give us more information. What do you mean by "delete partially"? Delete only certain records from a table? Remove information from certain columns in a table? Either way, whatever is passed into @tablename will need to have the same information for each table or you may need to create other @variables to contain your constraint (i.e., WHERE clause, etc) information.
I wasn't born stupid - I had to study.
-
Thanks for your note! In below script @tablename i need to delete partially rows instead of whole row in the table. please advise me how to fix this?
DECLARE table_cursor CURSOR READ_ONLY FOR
SELECT name,
CASE name
WHEN 'visits' THEN 1
WHEN 'patients' THEN 2
ELSE 3
END AS sortkey
FROM sysobjects
WHERE xtype = 'U'
AND dbo.tabletype(name) IN ('patient data', 'transient', 'obsolete')
AND EXISTS (SELECT * FROM sysforeignkeys WHERE sysforeignkeys.rkeyid = sysobjects.id)
ORDER BY sortkey, name
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @tablename, @sortkey
WHILE @@fetch_status = 0
BEGIN
-- Delete the rows of this table.
SET @sql = 'DELETE FROM ' + @tablename
PRINT @sql
EXECUTE (@sql)
FETCH NEXT FROM table_cursor INTO @tablename, @sortkey
END
CLOSE table_cursor
DEALLOCATE table_cursor
-
Hi Farell! Please advise how to fix above script with sample code to delete only partially rows in the table
Thanks
-
Please answer this so we can help you :
Do you want to drop existing columns from the table?
or
Do you want to delete information in certain columns?
or
Do you want to Delete certain rows from the tables?
In each case what's the condition to find which rows/cols need to be removed?
I cannot stress enough that without this information nobody in the world will be able to answer your question.
-
Remi is correct.
For the sake of argument, let's say all of your tables have a PatientID. You pass that information into your stored procedure into the variable @PatientID. Your delete then could read as follows, (you don't actually need the FROM):
SET @sql = 'DELETE ' + @tablename + ' WHERE PatientID = ' + CONVERT( varchar, @PatientID) -- I am assuming PatientID is an integer)
PRINT @sql
EXECUTE (@sql)
FETCH NEXT FROM table_cursor INTO @tablename, @sortkey
END
CLOSE table_cursor
DEALLOCATE table_cursor
This way you are only deleting one record.
This is a poor response as at least two of us are VERY uncertain as to exactly what you want to delete from these tables. Please give us more information.
I wasn't born stupid - I had to study.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply