|
Home > Archive > Microsoft SQL Server forum > April 2005 > Scripted delete rows
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Scripted delete rows
|
|
| rcamarda 2005-04-27, 11:23 am |
| Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:
delete student
where student_id not in (select student_id from tblStudent)
The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.
Any Ideas are appreciated.
| |
| Simon Hayes 2005-04-27, 1:23 pm |
|
"rcamarda" <rcamarda@cablespeed.com> wrote in message
news:1114613428.538202.213970@f14g2000cwb.googlegroups.com...
> Hi,
> I need to delete rows from my user tables dependant upon there non
> existence from another table:
>
> delete student
> where student_id not in (select student_id from tblStudent)
>
> The reasons is convoluted, simplest explanation is that our operational
> system allows the change of business keys. This wreaks havoc in the
> data warehouse.
> So, I'm look for help on how I can delete rows from tables that have a
> column STUDENT_ID. I'd like the script to search for the tables, then
> perform the delete.
> I don't know where information about user tables are stored, nor how to
> loop through the results to do the delete.
>
> Any Ideas are appreciated.
>
You can use a query like this to generate a script, then review it before
executing it:
select 'delete from ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
' where not exists (select student_id from dbo.tblStudent ts where ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
'.student_id = ts.student_id)'
from
INFORMATION_SCHEMA.COLUMNS
where
COLUMN_NAME = 'student_id' and
objectproperty(objec
t_id(TABLE_SCHEMA + '.' + TABLE_NAME), 'IsTable') = 1
See the INFORMATION_SCHEMA views in Books Online, as well as syscolumns,
sysobjects, and "Meta Data Functions".
Simon
| |
| rcamarda 2005-04-28, 7:23 am |
| Simon,
Works like a champ and I learned something new!
Thanks
Rob
|
|
|
|
|