Creating a View using a Cursor

Question

Creating a View using a Cursor

DamienB

SSC Eights!

Points: 926
More actions
July 7, 2008 at 12:30 am

#187800

G'day all!
I am finding myself creating quite a few views lately and wondered if there was a way I could use dynamic SQL to speed up the process and make it less fiddly.
I have 15 seperate databases which are for this purpose identical. I want to write a custom SELECT statement that is applied to each of the databases and have a view created. At the moment I copy and paste the select statement and unions manually, just changing the database name which is not a lot of fun.
I have created a table that holds all of the database names ready to be inserted into a string to have the view created, but I just cant get my head around the syntax. A cursor seems suited to this job from what I've read, but maybe someone can offer a better way? I can see the common suggestion to never use cursors if you can help it, and I don't want to form any unnecessary bad habits
Here is an example of one of my views;
SELECT [field1], [field2], [field3]
FROM database1.dbo.[Table]
UNION ALL
SELECT [field1], [field2], [field3]
FROM database2.dbo.[Table]
UNION ALL
SELECT [field1], [field2], [field3]
FROM database3.dbo.[Table]
and so forth all the way to 15... its a long way when you've got complex select statements, and especially when you have to amend them. Replace all is good, but its not great >.<
Any comments please! All suggestions welcome!
- Damien

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

DamienB (7/7/2008)
A cursor seems suited to this job from what I've read, but maybe someone can offer a better way? I can see the common suggestion to never use cursors if you can help it, and I don't want to form any unnecessary bad habits

That's probably one of the few things I would use a cursor for. Just don't get into the habit of using them for data manipulation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DamienB SSC Eights! Points: 926 More actions · Answer 2

Hi Gail

Thanks for the quick reply! Maybe I should have been more specific... I need help actually writing the code! >.<

- Damien

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ah. In that case, please could you post the schema for the table you have the DB names in, a couple of rows from that table and the result you would like to see.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DamienB SSC Eights! Points: 926 More actions · Answer 4

It is pretty long winded so I'll include two databases so it will just be a copy and paste job across the rest of them.

CREATE TABLE [ceo_dbs] (

[db_id] [int] NULL ,

[db_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[system] [int] NULL ,

[school] [int] NULL

) ON [PRIMARY]

GO

Database content:

db_name school

St Marys Primary 1

St Johns 1

St Josephs Colleg 1

So what I am trying to do is get the cursor to insert the @db_name variable for me in the following select statement until it runs out of databases, then creates the view.

create view v_student_addresses as

select s.first_name, s.surname, s.mail, m.address01, m.address02, m.suburb, m.postcode,

d.school_name

from @dbname.dbo.student as s

left join @db_name.dbo.mail as m on m.mkey = s.mail,

cross join school_data as d

and then union the results from another database.

Thank you for your time and interest! I could find very little on the net that I understood to get around this, so good chance other people with multiple identical DB's will benefit too

- Damien

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

I just posted a way to do somethng very similar, without cursors:

Create Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

--spLogin_OwnedObjects 'sa'

declare @sql varchar(MAX), @DB_Objects varchar(512)

Select @DB_Objects = ' L.name as Login, U.*

From %D%.sys.objects o

Join %D%.sys.database_principals u

ON Coalesce(o.principal_id, (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))

= U.principal_id

left join %D%.sys.server_principals L on L.sid = u.sid

'

Select @sql = 'SELECT * FROM

(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

+ Replace(@DB_objects, '%D%', [name])

From master.sys.databases

Where [name] = 'master'

Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

+ Replace(@DB_objects, '%D%', [name])

From master.sys.databases

Where [name] != 'master'

Select @sql = @sql + ') oo Where Login = ''' + @Login + ''''

print @sql

EXEC (@sql)

What you want to do is much simpler, so you should be able to adapt it from this. If you need help figuring it out, just say so.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

OK, here is a much simplified version that should work for you:

Create Proc spCreate_MultiDBView As

declare @sql varchar(MAX)

Set @sql = 'CREATE VIEW v_student_addresses As

'

Select @sql = @sql + Case RowNum When 1 then '' Else 'UNION ALL ' End

+ 'select s.first_name, s.surname, s.mail, m.address01, m.address02, m.suburb, m.postcode,

d.school_name

from '+ db_name +'.dbo.student as s

left join '+ db_name +'.dbo.mail as m on m.mkey = s.mail,

cross join school_data as d

'

From (Select Row_Number() Over (Order by db_name) as RowNum, * From ceo_dbs) R

print @sql

EXEC (@sql)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

DamienB SSC Eights! Points: 926 More actions · Answer 7

G'day mate,

Thank you very much for getting me started! I had to alter the code because I'm using SQL2000.

alter Proc spCreate_MultiDBView As

declare @sql varchar(6000)

Set @sql = 'CREATE VIEW v_student_addresses As

'

Select @sql = @sql + Case ident When 1 then ' ' Else 'UNION ALL ' End

+ 'select s.first_name, s.surname, s.school_year

from '+ '['+db_name + ']'+'.dbo.st as s where s.status = ''full''

and (s.school_year = ''y10'' or s.school_year = ''Y03'') '

From (Select * From cent_schools) R

print @sql

EXEC (@sql)

It looked complicated trying to replicate the RowNum/Over functions in SQL2000 so I thought bugger it and added an ident column to the cent_schools table which holds my database names The only other change I made was including the square brackets so the syntax didnt dump because of spaces etc.

Thanks very much to you and Gail for helping me out! and so quickly too! I will be getting a fair bit of use out of this one I assure you

- Damien

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Thanks for the feedback Damien.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Creating a View using a Cursor

Cookies on SQLServerCentral