'd like to find a more elegant an efficient solution than a cursor or a dynamic "exec @sql" loop for this little job which clears the staging tables in an SSIS package - all in SQL Server 2008.
It runs at a quiet time and as a sysadmin user and there are no constraints, referential integrity etc. just plain stand-alone tables so I could just use a cursor, but the "code artist" in me says there has to be a better way.
I gather that sp_msforeachtable is just a glorified cursor, so I'd rather roll my own than use that. It is planned to be a stored procedure called by an execute sql task (unless there's a better suggestion).
The skeleton script is:
Delete From dbo.<tablename> t Where (t.Company = @CoNo)
and (<tablename> in
(SELECT name FROM sys.objects WHERE type = 'U'
i.e. for every user table in the database with the name ending in "Staging" I want to delete all records where field company = @CoNo where @CoNo is the parameter passed in.
The company field is guaranteed to exist in every staging table too so no checks needed.
Any bright ideas welcome.