StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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 <code>sys.tables</code> and <code>sys.columns</code> between two databases and create INSERT/SELECT script.</p> <p><strong>Test Setup:</strong></p> <pre><code>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)) </code></pre> <p>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.</p> <p>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.</p> <p>In this case - tables T1, T2 and T8 will be copied. </p> <p><strong>Query:</strong></p> <pre><code>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 </code></pre> <p>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 <code>sp_executesql</code> row-by-row.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload