In Transact SQL for SQL Server 2008 R2 How do I do the following:

Question

0.00/5 (No votes)

See more:

A database consists of 100+ tables. Each table contains rows with 3 subsets of data based on the value in a common column. I want to copy the data for 1 subset at a time to a new database for that column with the same schema as the original database. Essentially the original database will be split into 3 parts.

For instance, each of the 100+ tables has a column 'location'. In each table there are multiple rows for locations 1, 2 and 3. I want to move all data for location 1 to a new database named loc1db, all data for location 2 to a new database named loc2db, and so on.

Thank you for your help

Posted 14-Sep-15 10:32am

JimRo

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-09-14T10:57:00

Solution 1

If I understand your question correctly, you can use for example INSERT INTO ... SELECT structure.

If the target database is in the same SQL Server, in the target database issue a command like

SQL

INSERT INTO NewTableName (column1, column2, ...)
SELECT col1, col2, ...
FROM SourceDatabase.owner.sourcetable a
WHERE a.Location = 1

If the target database is in another SQL Server instance, you can create a linked server between the databases. For more information, see Create Linked Servers (SQL Server Database Engine)[^]

Posted 14-Sep-15 10:57am

Wendelius

Comments

JimRo 14-Sep-15 17:22pm

Thanks Mika. The problem I have with this is that there are 100+ tables and each table has 100+ columns. I would have to create a copy of your solution for each table (100+) and would have to list every column (100+). 100 * 100 = 10000 So, the query would be 4000+ lines long (4*100+)and the first two lines would have to list every column.
It would probably be easier to copy the db to the new name and loop through every table deleting all rows <> the selected location.

Maybe you have an idea of how to generate that loop from the db schema on the fly.