October 1, 2008 at 6:01 am
Hi All,
I have four different databases having common structure. i.e tablename and column name. These databases are used for different products. i.e for Product1, the databasename is ProdData1...
I want to create one database in which i want to have the data from all the 4 databases. In this database i have created additional column (Product) in all the tables.
Now i want to import all the tables from all the databases with the identification of the Product in this database. Also new record and updations should also be updated in this database as soon as it is updated in any of the four databses.
Any suggestions will be highly appreciated. I am not sure whether this can be done through replication.
Thanks in Advance.
October 20, 2008 at 7:58 am
You should use DTS during a period of downtime (so that the source DB will not currently be getting updated), and populate the destination tables in the new database with the relevant source, other than the "Product" table which you have created which you can set to have a static value dependant on the DB the data is coming from.
You don;t say if there are a large number of tables, or if it is in a simple structure and whether you have created this "Product" field on each of the tables in the destination to act as a way of distinguishing the source of the tables you are going to be importing?
Triggers are the simplest way of automatically creating a record in one table after a record has been created on another table.
October 21, 2008 at 12:01 am
Use this query
SET IDENTITY_INSERT yourdatabasename.dbo.table1 ON
insert into table1 (col1, col2, col3, col4)
select col1, col2, col3, col4 from table2
SET IDENTITY_INSERT yourdatabasename.dbo.table1 OFF
But the best alternative is to use the DTS services
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply