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 * 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.

Just as a side comment, you really need a UNION, means that you need to get distinct rows from the union? It is a lot slower than UNION ALL, which just combine the two outputs. You stated "then take data out of our primary database and put it in to the new DB", which makes me think you want UNION ALL. — Dominic Goulet
– Dominic Goulet, Commented Mar 14, 2013 at 17:36
@DominicGoulet: You are correct... I forgot the ALL when I was typing in the sample SQL. — Caynadian
– Caynadian, Commented Mar 15, 2013 at 15:36
@MichaelFredrickson: I need to have a GO command because CREATE VIEW will not work unless it is the first statement in the script. — Caynadian
– Caynadian, Commented Mar 15, 2013 at 15:37

Dominic Goulet · Accepted Answer · 2013-03-14 17:33:32Z

5

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)

edited Mar 14, 2013 at 17:33

answered Mar 14, 2013 at 17:21

Dominic Goulet

8,1437 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sebastian Meine · Accepted Answer · 2013-03-14 17:05:01Z

3

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.

answered Mar 14, 2013 at 17:05

Sebastian Meine

11.9k1 gold badge34 silver badges44 bronze badges

2 Comments

Dominic Goulet Over a year ago

I expanded your solution into one that @Caynadian can use.

Michael Fredrickson Over a year ago

+1 I was somehow unaware that fully qualifying sp_executesql would make it run in the context of the qualified database...

Collectives™ on Stack Overflow

Create View in T-SQL Script

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related