Creating a large dynamic View

I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?

EDIT: Changed the title to read Procedurally generating a large view.you have a stored procedure that creates a view with dynamic sql? seems like a bad idea. stored procedures are for DML, not DDL.

why not just store the view definition as a script in source control and execute it as necessary? when your supplier adds columns to their tables, you just add those columns to your script and execute it again.|||I don't really know what you mean by storing it as a script in a source control. By a source control, do you mean a third party utility? I am not familiar with the term. I don't really have any third party utilities or compilers to work with, just SQL Server 2000. The stored procedure that I have has been working fine. I just wanted to find out if there is a more efficient way to do the same thing. Basically, I need the view to look at different tables every day. The tables are indicated at the end of the table name by day and month of the data they contain. The basic outline of the stored procedure I have is below.

Create Procedure ProcName
as
Declare @.DatabaseName as varchar (128)
Declare @.sql as varchar(8000)
Declare @.view_Name as varchar (128)
Declare @.table_Name as varchar (128)
Declare @.ProcDate as datetime
Declare @.cntr as Int
Declare @.sql2 as varchar(8000)
Declare @.sql3 as varchar(8000)


Select @.DatabaseName = DB_NAME()
exec usp_DayToProcess Null, @.ProcDate output

Set @.cntr = 0
Set @.view_Name = 'ViewName'
Set @.Path2 = ''
Set @.Path3 = ''
Set @.Path = 'CREATE VIEW ViewName AS SELECT * FROM ('

While (@.cntr < 15)
Begin
Set @.table_Name = '[TableName_' + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 1, 2) + SubString(Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 101), 4, 2) + ']'
if exists (select * from dbo.sysobjects where id = object_id(@.table_Name) and OBJECTPROPERTY(id, N'IsUserTable') = 1 and crdate > DateAdd(year, -1, @.ProcDate))
Begin
If @.cntr <> 0
Begin
Set @.Path = @.Path + 'UNION ALL '
End
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
End
Set @.cntr = @.cntr + 1
If Len(@.Path) > 7000
Begin
If Len(@.Path2) > 7000
Begin
Set @.Path3 = @.Path2
End
Set @.Path2 = @.Path
Set @.Path = ''
End
End

Set @.Path = @.Path + ') TempView ORDER BY ...'

EXECUTE (@.Path3 + @.Path2 + @.Path)
GO

Since I have several fields that have to be reformated from the tables as well as functions to perform on some of the fields in order to get the values I need, the sql gets fairly large. So, it ends up taking more than two varchar variables to store all of the sql to search 15 tables. I am trying to standardize the procedure a bit, so in case more than 15 days of tables are required, it would require more variables. I was wondering if there is a more efficient way of doing this with SQL Server 2000 alone.|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control|||What is the point of this:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_Name
Are you manually coding the column names?|||Personally, I like the SELECT * part|||The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels|||source control is part of how professionals write code. it allows you to see how the code has changed in time.

http://en.wikipedia.org/wiki/Revision_control

So, you are basically saying to modify the code each day/week/whenever it needs to be run?

What is the point of this:

Code:
Set @.Path = @.Path + 'SELECT ... FROM ' + @.table_NameAre you manually coding the column names?

The '...' is where I am specifying the fields to use. I didn't include all of it, because it is a bit long. For instance, I am adding a date field into the view so that the date of the transaction is a field. The tables do not have a transaction date field, since they are a different table for each day. Also, I specify the field names, because there are times that the company who creates the code that makes the tables will change that code during an update. I could check the table for any changes each time they put out updates, but this aggregates several tables. So, some of the tables would be missing fields that others have within the tables that are being aggregated. This would cause an error if the fields to use were not specified.

Personally, I like the SELECT * part

After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

Originally Posted by Ishe
The tables are indicated at the end of the table name by day and month of the data they contain.

That is just so wrong on so many levels

I know what you mean, but I didn't really design the tables or the code to make the tables. It's just the only thing I have to work with really.|||So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() (http://msdn2.microsoft.com/en-us/library/3y322t50.aspx) in C#. hard to debug, hard to know what the actual state of the system was at any given time.|||After the tables are aggregated in the view, they are wrapped with a SELECT * in order to put them in some semblence of order. I order them by the primary key, then by date with the SELECT *.

SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.|||SELET * has NOTHING to do with the ordering off a resultset.
For that you need an ORDER BY clause.

I know, and it has an ORDER BY clause at the end, but I have found from experience with prior UNION views that if you slap an ORDER BY clause at the end of the last union, it doesn't order the entire result set, just the last SELECT. So, I wrapped the entire UNION query making the UNION query a subquery and put the ORDER BY clause at the end of the wrapping query.

Originally Posted by Ishe
So, you are basically saying to modify the code each day/week/whenever it needs to be run?

yes, that's what I would do. There is great value in knowing what the definition of the view was at a certain time.

Also I don't like the idea of generating permanent database objects from a proc. If you do that, you are building on a very shaky foundation.

To me it's the same thing as writing self modifying code in a compiled app, for example by coding with Reflection.Emit() in C#. hard to debug, hard to know what the actual state of the system was at any given time.

Does that include creating tables through stored procedure?

In this case I am trying to create this in such a way that it won't take someone that knows anything much about SQL Server to be able to use the procedure. Since the tables that the user would need to use change on a daily basis, I don't know of any other way to accomplish this. I can't rewrite the code for them every day. I am actually trying to change the code to be less customized, not more so.|||The '...' is where I am specifying the fields to use.If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.|||If you have to manually code these anyway, what is the point of the sproc? I mean, if your code grabbed the columns names from the schema and automagically built the view, that would be one thing, but I'm having trouble understanding the overall purpose of this process.

The only reason that it is done in a stored procedure is because the tables that I have to work with are daily transaction tables. The table names indicate the day and month of the transactions that are contained within. I don't think I can create a standard view with daily changing table names.|||But you don't have standard columns!|||But you don't have standard columns!

I'm not sure what you mean. The below contains the code that I replaced '...' with in the example.

Set @.Path = @.Path + 'SELECT FB_MBRNO AS [Member No], Convert(datetime, ' + CHAR(39) + Convert(VarChar(10), DateAdd(day, -(@.cntr), @.ProcDate), 102) + CHAR(39) + ') AS [Tran Date], FB_TLR AS Tlr, FR_TRAN_CODE AS [Tran Code], FB_STATUS AS [Status], FB_CNV_CASH AS CnvCash, FR_TIM AS [Time], FB_CASH AS [Cash], FB_CHECK AS [Checks], FB_APPLIED AS Applied, FB_CASHBACK AS [Cash Back], FB_REVERSED AS Reversed, FB_MISC_CODE As [Misc Code], FB_TRAN_NO As [Tran No] FROM ' + @.table_Name

I think the columns are fairly standard, except the [Tran Date] field, which is there because the tables I am looking at do not have a date field that indicates when the transaction occured.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.|||if you are adding tables on a daily basis, then you have a bigger problem than this view it seems. that's a poor design.

I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.|||I guess you have no choice then. I wouldn't use that software provider again if I were you. ;)|||I agree with you in most cases (this one included), but it is the way our software provider designed it. Actually it is the way they designed most of the tables.

Have I mentioned lately I hate 3rd party vendors?

There is no silver bullet|||Have I mentioned lately I hate 3rd party vendors?Not often enough.

Creating a blank table from another

Hi again,

I want to create a blank table with the same column names as another but with no rows i.e. it is empty. Does anyone know how to do the last part?

I have so far

create table newtest as select * from shared.test .... (empty bit here?)

Any good advanced sql tutorial urls would be good to.

Thanks in advance :)select *
from OldTable
into Newtable
where 1 = 0

This will not copy triggers, indexes, and the such. Just column names and datatypes.|||Ok i will try that but why does that work?

Cheers :)|||select *
from OldTable
into Newtable

...selects columns from the old table into a Newtable (created on the fly).

where 1 = 0

...always evaluates to false (in my universe, but I'm a Democrat), so no rows match the filter criteria and thus no rows are actually inserted.|||Cheers, Thanks

:D|||U can try like This Also

create table NewTable
as select * from OldTable
where rownum < 1;

Even this will not copy triggers, Indexes and Constraints...|||if you need to copy the table as is (with all indexes, primary/foreign keys, triggers), you can use the option "generate sql script":

Enterprise Manager > Server > DataBase > Tables > put your cursor on the selected table/s > All Tasks (right mouse click) > Generate SQL Script

Then you get a wizard which allow you to create scripts that drops/create your selected objects. on the options tab you may decide if you would like to have the indexes, triggers, relationships...

If you want to create the same table BUT with different name, make sure you rename all the objects (indexes, primary/foreign keys, triggers) before running.

good luck|||Originally posted by Sowmyam
U can try like This Also

create table NewTable
as select * from OldTable
where rownum < 1;

Even this will not copy triggers, Indexes and Constraints...

Ahhhh...the smell of Oracle (Or is it UDB) in the morning...it smells like....confusion

USE Northwind
GO

CREATE TABLE myTable99
AS
SELECT * FROM Orders
WHERE rownum < 1;
GO


And nope...that won't work...|||Hi Brett,
I didn't get u clearly, are u telling

create table NewTable
as select * from OldTable
where rownum < 1;

this code won't work.

I tried it works.
Ok this query Creates a Table NewTable of same structure as OldTable and NewTable will not have any rows.
This won't copy any indxes, constraints and all, but creates a table.

pls give a feedback for this.

Thanks In Advance.|||Hi Brett,
I am very sorry. yes it is confusion.
it will work in Oracle, this is Microsoft SQL server, by thinking it is Oracle i replied.

I am very sorry about it.|||hi
just add truncate command after your code!!

hope this will solve ur issue.

Cheers

Deepak K

CREATE VIEW - script to automate column names?

Hi,

I'm trying to create views on all my existing tables and for that I'd
like to create a script or so.
I don't want to specify the '*' for the columns in the create view
statement. I prefer to specify the column names.
I have the column names int sys.columns table but Do not know how to
handle them to have a statement like that:

CREATE VIEW myVIEW
WITH SCHEMABINDING
AS
SELECT col1name, col2name, col3name, etc...
from sys.columns
...???....

Anyone can help?

thx,

ChrisOn 12 Mar 2007 04:44:56 -0700, "clir" <christophe.leroquais@.gmail.com>
wrote:

Use a cursor to loop over the column names, all the while
concatenating a string variable. In the end, execute that string
(sp_executesql) and your view will be created.

-Tom.

Quote:

Originally Posted by

>Hi,
>
>I'm trying to create views on all my existing tables and for that I'd
>like to create a script or so.
>I don't want to specify the '*' for the columns in the create view
>statement. I prefer to specify the column names.
>I have the column names int sys.columns table but Do not know how to
>handle them to have a statement like that:
>
>CREATE VIEW myVIEW
>WITH SCHEMABINDING
>AS
SELECT col1name, col2name, col3name, etc...
from sys.columns
...???....
>
>
>Anyone can help?
>
>thx,
>
>Chris