CODEWITHSUNDEEP
t-sqlsql-server-2008-r2
Caynadian
Caynadian

Reputation: 757

Create View in T-SQL Script

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 * from [' + @archdb + '].[dbo].artrans'
exec (@sql)

But it now complains about the GO statement (Incorrect syntax).

The name of the database being created for the archived data is determined dynamically in the script (@archdb contains the name) so I can't script in the DB name and I can't run a second script.

Upvotes: 3

Views: 2826

Answers (2)

Dominic Goulet
Dominic Goulet

Reputation: 8103

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)

Upvotes: 5

Sebastian Meine
Sebastian Meine

Reputation: 11773

to execute a dynamic SQL statement in a different database than the one you are in you can use sp_executesql like this:

USE db1;
EXEC db2.sys.sp_executesql N'SELECT DB_NAME();';

This wil result in db2 being returned.

GO is not a T-SQL statement. It is interpreted by SSMS to break the query text into batches. It never gets send to SQL Server itself.

Upvotes: 3

Related Questions