Copy filtered data from all tables to new database

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.

more ▼

asked Sep 27, 2016 at 07:26 PM in Default

avatar image

249 9 14 23

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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:

 select    'delete ' + name + ' where AcessibilityLevel = ''Low'';'
 from    sys.tables
 order by name;
more ▼

answered Sep 28, 2016 at 02:35 PM

avatar image

David Wimbush
10.7k 31 34 43

Thanks, but unfortunately the data needs to be filtered before it gets into the destination database.

Sep 28, 2016 at 02:48 PM siugoalie78

It's just easier to copy the whole database and then delete what you don't want rather than to copy the schema and then copy over the data you want. And the result is the same. Just saying.

Sep 28, 2016 at 03:25 PM David Wimbush

If i could, I would, believe me. But, that's not an option, unfortunately.

Sep 28, 2016 at 03:41 PM siugoalie78
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 27, 2016 at 07:26 PM

Seen: 77 times

Last Updated: Sep 28, 2016 at 03:41 PM

Copyright 2018 Redgate Software. Privacy Policy