Insert common data from one database into another?

Question

I have database X and database Y. X and Y have some tables and columns which are of the same schema. There is no data in database Y.

What SQL/T-SQL can I write in order to transfer all data from database X to database Y where the tables and column names are the same?

Thank you

Edit: Both databases are on the same server. We do not know which tables and columns are of the same name, so I cant insert into each table manually (e.g. there may be 100s of tables and columns with the same name)

You seem to have changed (or expanded) your question in the comments below; now you're asking about how to do this when "we do not know which tables and columns are the same". You need to explain how you want to match source and target tables and columns in this case, but the general solution is to generate an `INSERT...SELECT...` statement dynamically using metadata from `sys.tables` and `sys.columns`. See [this question](http://stackoverflow.com/questions/6844137/sql-insert-into-statement-but-suppose-you-do-not-know-origin-and-target-header), which is essentially the same. — Pondlife, May 14 '13 at 18:04

Knaģis · Answer 1 · 2013-05-14T14:44:10.677

2

If your databases are on different servers:

you need to create a linked server.
INSERT INTO database.schema.Table SELECT * FROM server2.database.schema.Table

If they are on the same server:

INSERT INTO database.schema.Table SELECT * FROM database2.schema.Table

edited May 14 '13 at 14:44

answered May 14 '13 at 14:38

Knaģis

20,827
7
66
80

1

Why are you assuming that both databases are on different servers?. Besides, the right way to query a table from another server is `LinkedServer.Database.schema.table` – Lamak May 14 '13 at 14:40
yes, made a mistake by omitting the database. Assumed that they are on different servers because otherwise it would be too simple... :) – Knaģis May 14 '13 at 14:42
I added some additional information to my question. Will this method work when we do not know which tables and columns are the same? (I can't run the insert query on each table individually) – Kurren May 14 '13 at 15:27

score 2 · Accepted Answer · answered May 15 '13 at 08:19

After receiving additional information from the question this turned to be a bit interesting so I tried to come up with a query that should do the the task of comparing sys.tables and sys.columns between two databases and create INSERT/SELECT script.

Test Setup:

USE X
CREATE TABLE t1 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t2 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t3 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t4 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t5 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t6 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t7 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t8 (co1 INT IDENTITY(1,1), col2 VARCHAR(10))

USE Y
CREATE TABLE t1 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t2 (co1 INT, col2 VARCHAR(10))
CREATE TABLE t3 (co1 VARCHAR(10), col2 VARCHAR(10))
CREATE TABLE t4 (co11 INT, col22 VARCHAR(10))
CREATE TABLE t5 (co11 INT, col2 VARCHAR(10))
CREATE TABLE t6 (co1 INT, col2 VARCHAR(10), col3 int)
CREATE TABLE t7 (co1 INT)
CREATE TABLE t8 (co1 INT IDENTITY(1,1), col2 VARCHAR(10))

I've tried to create few different scenarios that should be cover. Table in Y having additional or less columns, different datatypes, identities. There might be a whole more options I didn't thought of, but idea should be OK.

Also I've assumed that if two tables with same name have some identical columns but not all, transfer should not be done at all for those tables. If you'd like to also transfer those tables for matching columns, some JOINS should be tweaked but there is also an issue of whatever non-transfered columns in Y allow NULL or not.

In this case - tables T1, T2 and T8 will be copied.

Query:

WITH CTE_X AS 
(
    SELECT  xt.object_id, xs.NAME + '.' + xt.NAME AS tblName, COUNT(*) AS colsNo FROM x.sys.tables xt
    INNER JOIN x.sys.columns xc ON xc.object_id = xt.object_id
    INNER JOIN x.sys.schemas xs ON xt.schema_id = xs.schema_id
    GROUP BY xt.object_id, xt.NAME, xs.NAME
)
,CTE_Y AS 
(
    SELECT  yt.object_id, ys.NAME + '.' + yt.NAME AS tblName, COUNT(*) AS colsNo FROM y.sys.tables yt
    INNER JOIN y.sys.columns yc ON yc.object_id = yt.object_id
    INNER JOIN y.sys.schemas ys ON yt.schema_id = ys.schema_id 
    GROUP BY yt.object_id, yt.NAME, ys.NAME
)
,CTE_XY AS 
(
    SELECT xt.object_id, xt.tblName, COUNT(*) colsNO FROM CTE_X xt
    INNER JOIN x.sys.columns xc ON xc.object_id = xt.object_id
    INNER JOIN CTE_Y yt ON xt.tblName = yt.tblName AND xt.colsNo = yt.colsNo 
    INNER JOIN y.sys.columns yc ON yc.object_id = yt.object_id AND xc.name = yc.name AND xc.user_type_id = yc.user_type_id AND xc.precision = yc.precision AND xc.scale = yc.scale
    GROUP BY xt.object_id, xt.tblName 
)
,CTE_Tables AS
(
    SELECT xy.object_id, xy.tblName
    FROM CTE_XY xy
    INNER JOIN CTE_X x ON xy.colsNO = x.colsNo AND xy.tblName = x.tblName 
)
,CTE_Columns AS 
(
    SELECT c.object_id, c.name, c.is_identity FROM CTE_Tables t
    INNER JOIN y.sys.columns c ON t.object_id = c.object_id
)
,CTE_ColConc AS 
(
    SELECT  OBJECT_ID, 
            STUFF((SELECT ', ' +  name 
                   FROM   CTE_Columns c2 
                   WHERE  c2.OBJECT_ID = c1.OBJECT_ID 
                   FOR XML PATH('')), 1, 2, '')  Cols,
            MAX(CAST(c1.is_identity AS INT)) AS hasIdentity
    FROM   CTE_Columns c1 
    GROUP  BY c1.object_id
)
SELECT 
      CASE WHEN hasIdentity = 1 THEN 'SET IDENTITY_INSERT Y.' + tblName + ' ON; ' ELSE '' END 
    + 'INSERT INTO Y.' + tblName + ' (' + Cols + ') SELECT '+ Cols + ' FROM X.'  + tblName + ';' 
    + CASE WHEN hasIdentity = 1 THEN 'SET IDENTITY_INSERT Y.' + tblName + ' OFF;' ELSE '' END
FROM CTE_Tables t 
INNER JOIN CTE_ColConc c ON c.OBJECT_ID = t.object_id

Result of the query will be script with INSERT/SELECT statements. You can then copy it to new query window and double check it before running. If you need automated process - just get the results into #temp table at the end and run sp_executesql row-by-row.

score 0 · Answer 3 · answered May 15 '13 at 16:29

0

Still not sure why one could not use the SSIS import export wizard for a simple transfer of data from one database to another, same table schema?

Right click database to export, tasks, export data...

answered May 15 '13 at 16:29

Steve Ruscitto

1

I considered this. But my situation needs a bit more tweaking. For example, I want to transfer all data from DB X to DB Y where the table and column names are the same, and the destination table has 0 rows. So I thought maybe an SQL query will let me specify conditions like that? – Kurren May 16 '13 at 10:43

Insert common data from one database into another?

3 Answers3