October 8, 2008 at 3:27 am
Hi,
is it possible to do something like description below?
for each view in DB:
- create permanent table with name based on view (for example VwConsignment -> TblConsignment) if not exists
- fill this table with data retrieved by SELECT statement from view
October 8, 2008 at 6:25 am
Yes, it is possible. You could do it for example using dynamic SQL.
select * from sys.views
select * from sys.tables
will help you to find all missing tables. Once you have identified that a table has to be created, you can create the table using
SELECT * INTO tblSomething
FROM vwSomething
Now I said that you can do it, but a question remains whether it is a good idea. That depends on where and how you play to use this SQL. If you're planning to use it in production, and there is lots of data in these views, SELECT INTO can cause problems with locking and I wouldn't recommend it. If you just want to do it once, or do it on a database that will be without other traffic at the time of execution, it should be OK.
Well, if you decide to do it, the only thing remaining is to write some kind of loop that will use dynamic SQL and the abovementioned data, and execute it.
If you want to be very accurate, you could also use sys.columns (which contains also definition of view columns) and code the CREATE TABLE and following INSERT from there (using dynamicSQL).
October 8, 2008 at 6:48 am
I want to do this processing on replication snapshot used for reporting and I want to do it during night so I don't suppose any problem with locking.
And I know that there are system tables and that I can use dynamic SQL. But I am not sure about right way (I would prefered some set-based solution) and I don't know where exactly (which columns, which information) SELECT statements for DB views are stored...
October 8, 2008 at 7:58 am
OK, now we know a bit more about how you want to use it, and I hope you'll get some assistance soon. I know almost nothing about replication, so I won't try - there can be some good solution I don't know about, and it's no use inventing the wheel.
October 9, 2008 at 2:50 am
Any suggestions? 🙁
What I have found out until now is:
select OBJECT_DEFINITION(object_id) from sys.objects where type = 'V' and is_ms_shipped = 0
Is it right way to do my job? I would cut begining of the result to the first AS and I should have the SELECT form each view, I hope...
October 9, 2008 at 3:17 am
I cant use 'AS' to find SELECT statement begining 🙁 Those characters can be used inside view name. And I have also problem with creating table structure according view definition. I have supposed, that I will only add INTO [tablename] before first FROM substring but there can be some subquery inside view so I don't know what should I do...
October 9, 2008 at 4:16 am
OK, I have to solve it by own....
I have just written SELECT statement separating View's SELECT statement:
select STUFF(STUFF(OBJECT_DEFINITION(object_id),1,
LEN(name) + 1 + CHARINDEX(name,OBJECT_DEFINITION(object_id)),''),
1,2+CHARINDEX('AS',STUFF(OBJECT_DEFINITION(object_id),1,
LEN(name) + 1 + CHARINDEX(name,OBJECT_DEFINITION(object_id)),'')),'') from sys.objects where type = 'V' and is_ms_shipped = 0
:crazy:
October 9, 2008 at 5:48 am
Oh, I am fool, as Vladan said before, I only need a view name and then do SELECT * INTO table FROM view....
October 9, 2008 at 6:01 am
So my solution is below. But Is it possible to do without cursor?
DECLARE @sql_vw nvarchar(max)
DECLARE @sql_tbl nvarchar(max)
DECLARE @name nvarchar(max)
DECLARE c_view CURSOR FAST_FORWARD
FOR
select object_name(object_id)
from sys.objects
where type = 'V' and is_ms_shipped = 0
OPEN c_view
FETCH NEXT FROM c_view
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_tbl = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = ' + CAST(OBJECT_ID(STUFF(@name,1,2,'Tbl')) AS varchar(15)) + ' AND type in (' + CHAR(39) + 'U' + CHAR(39) + '))
DROP TABLE ' + STUFF(@name,1,2,'Tbl')
--print @sql_tbl
exec(@sql_tbl)
SET @sql_vw = 'SELECT * INTO ' + STUFF(@name,1,2,'Tbl') + ' FROM ' + @name
--print @sql_vw
exec(@sql_vw)
FETCH NEXT FROM c_view
INTO @name
END
CLOSE c_view
DEALLOCATE c_view
October 9, 2008 at 6:36 am
In this particular case, I don't believe a cursor is a bad solution to go with. What you are doing isn't really set based to start with.
That said, it wouldn't be hard to generate the entire exec @sql statements generated and put into a table, but unless 2K5 has some way to exec lines from a table without any kind of loop(which would really be the same as a cursor), I don't see how you're going to perform table drops, table creates and bulk data insertion without some sort of looping structure.
October 9, 2008 at 8:44 pm
Heh... I agree... this is what cursors are for...
...but then you'd have to admit to actually putting one in production. 😛
In SQL Server 2005, there is absolutely no reason to use a cursor for this...
DECLARE @sql VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL+CHAR(10),'')
+ 'SELECT * INTO '+Table_Catalog+'.'+Table_Schema+'.tbl'+SUBSTRING(Table_Name,3,128)
+ ' FROM '+Table_Catalog+'.'+Table_Schema+'.'+Table_Name
+ ' WHERE OBJECT_ID('+QUOTENAME(+Table_Catalog+'.'+Table_Schema+'.tbl'+SUBSTRING(Table_Name,3,128),'''')+') IS NULL'
FROM Information_Schema.Views
WHERE LEFT(Table_Name,2) = 'Vw'
PRINT (@SQL)
Just change the PRINT to EXEC and you're good to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2008 at 9:40 pm
Heh, I briefly thought about putting all of that into one huge string to exec... but that kinda thing just makes me a bit nervous. No practical reason for it, but it just seems like too much change for one statement to make :P. Somehow, the idea of it doing it one view at a time in a loop makes it seem less scary.
October 9, 2008 at 11:30 pm
Heh... I agree with that, Seth... that's why I'll always print it out and try a couple of pieces first, just to be sure. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2008 at 12:47 am
Thanks Jeff! I hoped you would help me. This was for the first time I was not able to find set-based solution and I am glad I don'nt need it at all 😀 It seems like you search "cursor" keyword in all formus each day :laugh:
October 10, 2008 at 5:13 am
Thanks for the feedback, Michal. Heh.. No... no searches for the word "cursor" or "while"... that would easily become a full time job on this forum alone. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply