sql update data in 2nd db from 1st db only where table exists

Question

I'm trying to move some tables from a live db to another db, which will then be made live after the data has been transferred. The 2nd db exists on a server with 2008, and the migration is from 2012. which has caused a few problems

Originally I did a full DB export with Scripts. because the export tool wouldnt go to previous versions. - after the full export I deleted the superfluous tables on db2. as I am only moving 40 or so out of about 200.

Now the DB structure is set up, all is working. with out of date-data. and everything is ready to be switched once the data is made up to date. So i'd ideally like a script that checks to see if a table in db1 exists in db2, then if it does to copy across all rows. Is this possible?

Answer 1

If creating a linked server is not an option then you can try using sql server import data wizard to transfer data from one db to another. Use the following link for how-to: Import data easily in sql server

Answer 2

You'll need to create a procedure on DB2 that returns a result based on the table you're checking:

USE [DB2]

CREATE PROCEDURE DBO.CHECKDB2TABLE

@TABLENAME NVARCHAR(50)

AS

IF EXISTS (
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE (TABLE_NAME = @TABLENAME)
)
    BEGIN
        SELECT 'TRUE'
    ELSE 
        SELECT 'FALSE'
    END

Then use the procedure to check DB2 and create a conditional statement based on that output to copy across the table.

EXEC DB2.dbo.CheckDB2Table @TABLENAME='Tablename'

Thanks.