August 18, 2012 at 11:51 am
Hello,
there are two similiar databases given. They are widely even identical though have some different columns and tables.
I am looking for a script which does the following:
Compare EACH datatable in db1 with dbNew (db1<dbNew).
dbNew does not contain any data, only the target structure db1 should be.
Db1 contains data, instead.
dbNew contains fully working tables, functions, views, and sps.
Db1 has to be updated to new columns, sps etc....
If a datatable in db1 does not exist, it shall be created.
I have tried Red Gate SQL Compare and SQL Data Compare but without any success.
Also: The DTS "Import data" to a database did not work properly, either.
However should data be imported to a table with a difference number of columns e.g. ?
Please, does any one has an idea how to synchronize this loved database ?
Thank you so much for any hints !
Stephan
August 18, 2012 at 12:28 pm
StephenNL (8/18/2012)
Hello,I have tried Red Gate SQL Compare and SQL Data Compare but without any success.
Also: The DTS "Import data" to a database did not work properly, either.
However should data be imported to a table with a difference number of columns e.g. ?
Why didn't this work?
You know if DB1 has data, it's not as simple as you think to update the structure. You could cause data loss or problems picking a new structure.
Disclosure: I work for Red Gate.
August 18, 2012 at 3:03 pm
I never dreamt of an answer from the SQL-Guru himself. 😉
August 19, 2012 at 11:11 am
I have played around with sql a bit and found a way to copy the tables and its data.
Unfortunately, "SELECT INTO" does not create the indexes of a table.
At least, I did not found a possibility.
Does any one have an idea how to copy an existing index to another database in TSQL ?
USE testdb;
DECLARE @table varchar(128)
DECLARE CUR CURSOR FOR
SELECT sysobjects.name FROM sysobjects WHERE type = 'U'
OPEN CUR
FETCH NEXT FROM CUR INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[testdb].[dbo].['+@table+']') AND type in (N'U'))
BEGIN
PRINT 'Working at table:'+@table
EXECUTE ('SELECT * INTO testdb.dbo.'+@table+' FROM SourceDB.dbo.'+@table)
/* How to create indexes from SourceDB ? */
END
FETCH NEXT FROM CUR INTO @table
END
CLOSE CUR
DEALLOCATE CUR
August 20, 2012 at 7:37 am
You can use SQLCompare to do this. Limit the filters to indexes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply