September 11, 2014 at 1:40 pm
I am beginner to Sql Server 2008 R2 T-SQL. Please somebody help me in accomplishing tasks as listed below using sql scripts.
I have 3 different databases on same sql server which contains many tables.
Database A - Consists of 25 tables.
Database B - Consists of 30 tables.
Database C - Consists of 15 tables.
Some tables exists in all 3 databases but with different data. Also some tables exists in 2 databases (data could be same or different) and some exists only in 1 database.
I want to achieve the following tasks using SSIS 2008 package:
(1) I want to merge all the above 3 databases into one new database on the same sql server.
(2) I want to bring all the tables along with data from above 3 databases to the new database.I want to combine common tables into one along with data and move to the new database.
(3) Also I want to bring all the functions, stored procedures & views from all above 3 databases to the new database.
Thanks in advance for any kind of help.
September 11, 2014 at 2:32 pm
I forgot to mention earlier that few tables might not have same number of columns.
September 11, 2014 at 2:53 pm
miskeens (9/11/2014)
Thanks in advance for any kind of help.
If you don't know how to write simple SQL queries (SELECT, UPDATE, INSERT, DELETE, CREATE TABLE) then you have 2 options:
1. Learn SQL.
2. Hire a professional who can do the job for you.
If you decide to use option 1 then there are tons of books about SQL. No one here wants to spend time to teach you TSQL basics.
In general your task can be split into subtasks:
1. Define common tables and columns
2. Determine if there are FK constraints. If yes then decide on the strategy of how you are going to keep the references. Options are: remap all the IDs to the new values or create new surrogate keys in separate column.
3. Create tables in a new database.
4. Write and run INSERT, SELECT, UPDATE statements to copy the data from all the databases into one and update FK if needed.
5. Compare, manually or using a tool (preferred) all the other objects (procedures, functions etc.) and merge the code if needed. Then deploy the scripts to the new database.
September 11, 2014 at 7:38 pm
To add to Alexander's advice... (which is spot on, BTW)...
The major wrinkle is going to be the fact that the source tables don't all have the same number of columns. That means you would have to query the system tables to get the column names and datatypes. For example...
SELECT TABLE_CATALOG AS InDatabaseName
, TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
, *
FROM TSQL2012.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'yourdatabaseName';
To create the tables, you could use dynamic SQL... but since that's reasonably advanced, that's probably out of the question....
ONE way that might work is to do something like...
SELECT DISTINCT TABLE_CATALOG,
, TABLE_NAME
, COLUMN_NAME
FROM [YourDBName].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG IN ('database1', 'database2', 'database3')
ORDER BY TABLE_NAME
, COLUMN_NAME
, TABLE_CATALOG;
That would at least give you the table names and column names. You can also include the data types and sizes if you include those columns. Once you had all that together, you could start on the INSERT/MERGE stuff...
September 12, 2014 at 8:27 am
Thank you very much for your help & suggestions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply