I have a SQL Server 2008 R2 database that has a table titled “Accessibility,” which contains lookup data, for the sake of simplicity let’s call it “High,” “Medium,” and “Low.”
Every other table in the database has a foreign key back to this table, so every row in every table has its own Accessibility level. This determines what data users get to see in the application. For example, a user with lower privileges can only see data marked “Low,” whereas a user with “High” privileges can see all data.
Is there a way to move only the “Low” data into a new, empty database with the same schema? I know I can write individual queries to move the data Where Accessibility = “Low” but is there a better way? I know I can use the import/export wizard, but do not see any filtering options there. Maybe an SSIS package, but I don’t have a lot of experience with those.
asked Sep 27, 2016 at 07:26 PM in Default
If this is a one-off thing, I would start with restoring a full backup to a new database name and then deleting the data that doesn't match your desired level. You could generate a script for the deletes with something like this:
answered Sep 28, 2016 at 02:35 PM