October 29, 2009 at 8:44 pm
vision59 (10/29/2009)
Thanks for your suggestion Jeffrey. You are right it works great for table by table process. I want to add new rows based on primary keys in database A to all tables in database B using identity column or foreign key reference how to do it?
Sorry, but you are going to have to set it up table by table. Or, you can download SQL Data Compare from Redgate (trial edition is a full edition). And, I believe you stated this is a one off - so, you wouldn't need to buy anything.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 30, 2009 at 6:23 am
I really hate writing dynamic sql that takes a table name in for a parameter for sql injection and query plan reasons but I figured this would be the easiest solution. I just wrapped the snippet Jeffery wrote into a stored proc and allowed the table name to be passed in as a variable. In its current form it doesn't handle identity inserts and it relies on the fact that both tables have identical schemas. Check SQL Server books online if you have to handle identity inserts. I didn't have a chance to run this so I apologize for any syntax errors.
CREATE PROCEDURE dbo.procname
@table_name varchar(255)
as
BEGIN
SET NOCOUNT ON
DECLARE @sql_command nvarchar(max)
SET @sql_command = '
/* Insert into B where record not in A and there are no identity columns present. */
INSERT INTO db2.dbo.[' + @table_name + ']
SELECT *
FROM db1.dbo.[' + @table_name + '] a
LEFT JOIN db2.dbo.[' + @table_name + '] b ON b.ID = a.ID
WHERE b.ID IS NULL
SELECT @@ROWCOUNT -- select out the # of rows affected
'
EXEC (@sql_command)
END
GO
------------------
Then run this to call the proc on all tables
DECLARE @sql_command nvarchar(max)
SET @sql_command = ''
SELECT @sql_command = @sql_command +
'EXEC dbo.procname @table_name=''' + table_name + '''; '
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
EXEC (@sql_command)
-- This should get you started.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply