September 25, 2018 at 10:22 pm
I have two databases with a couple hundred tables in them each. The tables in the two databases are 90% the same, with about 20 different tables in each. I'm working on a sproc to update database2 with the data from the tables it shares in database1.
I'm thinking truncate the tables and then insert the records from the tables in the other database like:
truncate table database2.dbo.table2
select * into database2.dbo.table2 from database1.dbo.table1
Is this the best way to do this, and is there a better way to do it than writing a couple hundred of these statements?
September 25, 2018 at 11:56 pm
Take this with a grain of salt, because I'm a SQL Server dummy, but I'd probably create a cursor to loop over the tables in your database that you want to truncate and then insert into. Then you could create variable to hold your insert and delete statements and use EXEC(@sqlstatement) to execute the dynamic SQL
This is a handy article: https://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure
Here's some code I wrote while working on this... Don't just copy & paste and run this ... it would do very bad things to your database!!
DECLARE @tableName VARCHAR(50);
DECLARE @sqlquery NVARCHAR(500);
DECLARE db_cursor CURSOR FOR
SELECT [name]
FROM Scratchpad.sys.all_objects sc
WHERE sc.Schema_ID = 1
AND sc.type = 'U'
INTERSECT
SELECT [name]
FROM SCRIDB.sys.all_objects sc
WHERE sc.Schema_ID = 1
AND sc.type = 'U';
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
-- create the TRUNCATE statement with the table name from the cursor.
SELECT @sqlquery = 'TRUNCATE TABLE ' + @tableName;
EXEC (@SQLquery)
-- do same with insert SELECT INTO statement.
SELECT @sqlquery = 'select * into database2.dbo.' + @tableName + ' from database1.dbo.' + @tableName
EXEC (@SQLquery)
FETCH NEXT FROM db_cursor INTO @tableName
END
CLOSE db_cursor
DEALLOCATE db_cursor
To get the tables that exist in both databases, you could use something like this:SELECT [name]
FROM Scratchpad.sys.all_objects sc
WHERE sc.Schema_ID = 1
AND sc.type = 'U'
INTERSECT
SELECT [name]
FROM SCRIDB.sys.all_objects sc
WHERE sc.Schema_ID = 1
AND sc.type = 'U';
Then you could pass those values to the other code that writes the dynamic SQL. Note that this doesn't guarantee that the tables in the two databases have the same structure.
September 26, 2018 at 7:17 am
scotsditch - Tuesday, September 25, 2018 10:22 PMI have two databases with a couple hundred tables in them each. The tables in the two databases are 90% the same, with about 20 different tables in each. I'm working on a sproc to update database2 with the data from the tables it shares in database1.I'm thinking truncate the tables and then insert the records from the tables in the other database like:
truncate table database2.dbo.table2
select * into database2.dbo.table2 from database1.dbo.table1
Is this the best way to do this, and is there a better way to do it than writing a couple hundred of these statements?
There is another option: SQL Server Replication. It could be set up to only deal with those specific tables that are 100% identical in structure between the two databases. More work to set it up, but probably easier than just creating a ton of dynamic SQL. However, that will keep the tables in sync ALL the time, and it's not without some level of performance cost.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
September 26, 2018 at 8:05 am
Would the MERGE statement be of value here? There is a good article here:
https://www.mssqltips.com/sqlservertip/5373/sql-server-merge-statement-usage-and-examples/
September 26, 2018 at 8:47 am
scotsditch - Tuesday, September 25, 2018 10:22 PMI have two databases with a couple hundred tables in them each. The tables in the two databases are 90% the same, with about 20 different tables in each. I'm working on a sproc to update database2 with the data from the tables it shares in database1.I'm thinking truncate the tables and then insert the records from the tables in the other database like:
truncate table database2.dbo.table2
select * into database2.dbo.table2 from database1.dbo.table1
Is this the best way to do this, and is there a better way to do it than writing a couple hundred of these statements?
Since the tables already exist you won't be able to use SELECT INTO, you will need to use INSERT INTO. Also, how many rows of data are there in each table you want to move?
September 26, 2018 at 9:07 am
sgmunson - Wednesday, September 26, 2018 7:17 AMscotsditch - Tuesday, September 25, 2018 10:22 PMI have two databases with a couple hundred tables in them each. The tables in the two databases are 90% the same, with about 20 different tables in each. I'm working on a sproc to update database2 with the data from the tables it shares in database1.I'm thinking truncate the tables and then insert the records from the tables in the other database like:
truncate table database2.dbo.table2
select * into database2.dbo.table2 from database1.dbo.table1
Is this the best way to do this, and is there a better way to do it than writing a couple hundred of these statements?
There is another option: SQL Server Replication. It could be set up to only deal with those specific tables that are 100% identical in structure between the two databases. More work to set it up, but probably easier than just creating a ton of dynamic SQL. However, that will keep the tables in sync ALL the time, and it's not without some level of performance cost.
I was assuming this was a one-off. Silly me. =)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply