May 27, 2015 at 9:18 am
Hi All,
We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50))
INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #SourceDatabase
DROP TABLE #SourceDatabase
CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50))
INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #ArchiveDatabase
DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1]
CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2]
CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3]
CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4]
CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5]
CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
any help is appreciated.
Many Thanks,
Alvin
May 27, 2015 at 9:45 am
I'm not sure why you would want to do that. Those views would just end up with the same kind of resultset as the following:
SELECT *
FROM TABLE1
UNION ALL
SELECT *
FROM TABLE1
Is there some kind of good reason to duplicate the records in ALL the tables? And what does the eixstence of any given VIEW do for you that a query like the one above can't do?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2015 at 10:26 am
Sorry i guess the statement 'Archive DB is a copy of the Main DB' is not correct.
Data in the Source Database and Archive Database is different. so we want to create views on a 3rd combined DB and point all our existing 300+ reports to the 3rd database . that's the ultimate reason.
May 27, 2015 at 12:30 pm
myjobsinus (5/27/2015)
Sorry i guess the statement 'Archive DB is a copy of the Main DB' is not correct.Data in the Source Database and Archive Database is different. so we want to create views on a 3rd combined DB and point all our existing 300+ reports to the 3rd database . that's the ultimate reason.
Okay, how about this:
DECLARE @ORIGINAL_DBNAME AS nvarchar(50) = N'DATA_WAREHOUSE';
DECLARE @SECOND_DBNAME AS nvarchar(50) = N'TEST_DB';
DECLARE @SQL AS nvarchar(max);
SET @SQL = 'WITH TABLES AS (
SELECT name FROM ' + @ORIGINAL_DBNAME + '.sys.tables
)
SELECT ''CREATE VIEW dbo.VW_'' + T.name + '' AS SELECT * FROM ' + @ORIGINAL_DBNAME + '.'' + T.name + '' UNION ALL SELECT * FROM ' + @SECOND_DBNAME + '.'' + T.name + '';''
FROM TABLES AS T;
';
PRINT @SQL;
EXEC (@SQL);
Once you modify the database names to your requirements, the result set will have one record for each table, and you can copy the entire result set into a new Query window in SSMS and then connect it to the 3rd database and run it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2015 at 1:09 pm
Another option to generate code to be run on the other server:
DECLARE @view_template varchar(8000)
SET @view_template = '
CREATE VIEW [$schema$].[$table$]
AS
SELECT * FROM [SourceDatabase].[$schema$].[$table$] UNION ALL
SELECT * FROM [ArchiveDatabase].[$schema$].[$table$]
GO
'
SELECT REPLACE(REPLACE(@view_template,
'$schema$', CAST(s_schema_name AS varchar(128))),
'$table$', CAST(st.name AS varchar(128)))
FROM SourceDatabase.sys.tables st
CROSS APPLY (
SELECT name AS s_schema_name
FROM SourceDatabase.sys.schemas ss
WHERE
ss.schema_id = st.schema_id
) AS s_assign_alias_names
INNER JOIN ArchiveDatabase.sys.tables [at] ON
[at].name = st.name
CROSS APPLY (
SELECT name AS a_schema_name
FROM ArchiveDatabase.sys.schemas [as]
WHERE
[as].schema_id = [at].schema_id
) AS s_assign_alias_names
WHERE
s_schema_name = a_schema_name
ORDER BY
s_schema_name, st.name
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 27, 2015 at 1:31 pm
SSCommitted - Perfect! works like a charm! Thank you so much. your help is much appreciated.
SSCrazy - Thanks for your help too.
Great work. keep going guys.
Many Thanks,
Alvin
May 27, 2015 at 1:47 pm
myjobsinus (5/27/2015)
SSCommitted - Perfect! works like a charm! Thank you so much. your help is much appreciated.SSCrazy - Thanks for your help too.
Great work. keep going guys.
Many Thanks,
Alvin
Alvin (myjobsinus),
SSCommitted and SSCrazy are a sort of forum title based on actions each of us has taken within the forum. To address us specifically, you should use the member id (yours is myjobsinus, mine is sgmunson, and SSCrazy is ScottPletcher). FYI...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply