Create View in T-SQL Script

Question

We are running SQL Server 2008 R2 and creating an archiving function that will create a new database (that can later be taken offline and stored elsewhere), then take data out of our primary database and put it in to the new DB and finally, create a view in the primary DB to look at the archived data in the new table. I have the script to create the DB, create the archive table in the new DB, copy the records from the primary DB and put them in to the archive DB and delete the records from the primary DB. Now I am trying to script the creation of a view: ``` declare @sql varchar(8000) set @sql = 'create view [' + @srcdb + '].[dbo].[vw_artrans] as select * from [' + @srcdb + '].[dbo].artrans union select * from [' + @archdb + '].[dbo].artrans' exec (@sql) ``` But you cannot pass the name of the DB to create view. So I tried this instead: ``` declare @sql varchar(8000) set @sql = 'use ' + @srcdb + ' go create view [vw_artrans] as select * from [' + @srcdb + '].[dbo].artrans union select…

Accepted Answer

Based on @Sebastien answer, here is the solution :

```
declare @sql varchar(8000)

set @sql = 'EXEC ' + @srcdb + '.sys.sp_executesql N''create view [vw_artrans] as 
            select * from [' + @srcdb + '].[dbo].artrans
            union
            select * from [' + @archdb + '].[dbo].artrans'';'

exec (@sql)
```

Create View in T-SQL Script

Solution

Problem