| Author |
 Topic  |
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
 Posted - 10/03/2006 : 08:58:08
|
quote:
they use the same text book, or having the same teacher
I don't think any book or teacher is dumb enough to recommend that kind of table design and dynamic sql.
May be one of them is a mentor of other 
Harsh Athalye
India.
"Nothing is Impossible" |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 10/03/2006 : 11:30:57
|
quote:
Originally posted by funketekun
quote:
Originally posted by tkizer
The SELECT portion needs to be dynamic.
Tara Kizer
why?
Because otherwise you'll get an error.
What Peso has shown you with EXEC is dynamic SQL.
Tara Kizer |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 11:41:32
|
[/quote]
CREATE VIEW vwMyHugeView
AS
SELECT *, '00000' AS TableName FROM [00000]
UNION ALL
SELECT *, '00001' AS TableName FROM [00001]
UNION ALL
SELECT *, '00002' AS TableName FROM [00002]
UNION ALL
SELECT *, '00003' AS TableName FROM [00003]
[/quote]
peso, anyway to do it without typing the table name ([00000]) 500 times?
p.s. the table names are different numbers. They are not 00001...00500 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/03/2006 : 11:48:37
|
Write a macro in your editor?
or do the equivalent in SQL:
SELECT 'SELECT *, ''' + TABLE_NAME + ''' AS TableName FROM [' + TABLE_NAME + '] UNION ALL '
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME
Kristen |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/03/2006 : 11:50:30
|
quote:
Originally posted by Kristen
Write a macro in your editor?
Kristen,
Is it really possible to write macro in QA? or you are implying some other editor?
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/03/2006 : 11:58:29
|
Not that I know of, I was implying some other editor 
Kristen |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 10/03/2006 : 12:20:51
|
quote:
Originally posted by Kristen
Not that I know of, I was implying some other editor
Kristen
For a moment, I believed you knew some nice cool trick to customize the QA using macros...buddy, that would have made life lot more easier !
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 13:26:24
|
kristen, thats a nice trick.
thanks |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 14:07:31
|
peso,
I created the hugeView, but doesn't the performace going to decrease drastically? As the view is gonna have millions of records. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 10/03/2006 : 14:14:40
|
Well you'd need to use a partitioned view in order to see performance gains. That's got huge scope though.
Dynamic SQL will cause performance issues as well.
Why not just write inline SQL in your application that handles which table to go to?
Your best performance solution would be to redesign your database so that you don't have all of these different tables with the same layout.
Tara Kizer |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 14:31:27
|
tkizer,
what is inline sql?
inline sql = non dynamic sql?
|
Edited by - funketekun on 10/03/2006 14:34:35 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 10/03/2006 : 14:35:34
|
It's when you call your select/insert/update/delete statements directly from your application and not using stored procedures. There is no benefit to using stored procedures in this case.
We don't allow any access to the database except via stored procedures, so you wouldn't be able to go down this route if you worked with me. But then again I'd require you to change your database design so that dynamic SQL wasn't required, plus to be properly normalized.
Tara Kizer |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 14:47:47
|
tkizer,
I can talk to the guy to redesign it. But I don't know how to redesign it. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 10/03/2006 : 15:00:38
|
You do understand the basic concept we are talking about, right?
If you have 3 tables like this:
Emp0001
Emp0002
Emp0003
and each table contains the name, title, dept, and hire date for a single employee, and the table name contains the employee number, there is no easy way to query all of your employees at once -- they are all in different tables! Thus, you have to dynamically query them one by one and combine the results, or you have to write a view to combine them all and then query the view. And, of course, to add a new employee, you must add a new table! And then alter your view that combines them all and so on.
Instead, the design should simply be:a single table of employees, where each row in the table represents an employee. i.e., like this:
Employees
EmployeeNumber (Primary key), Name, Title, Dept, Hire Date
0001,Jeff,....
0002,Ed,....
0003,Joe,....
..etc...
And now, you can query all employees at once. Adding a new employee is adding a row to a table -- very simple and easy. Deleting an employee is deleting a row. Querying a specific employee is done with a simple "WHERE EmployeeNumber=xxxx" clause on a SELECT. You can sort and filter and do updates and anything you want with basic, simple SQL statements. In other words, that's how things are supposed to work!
Does this basic concept make sense? Can you see now why the design is so bad?
I would really take the time to be sure that this all makes sense, ask questions here if you are not sure, and then discuss this with whoever designed the database. Because working with a design like this is a nightmare -- even simple little SQL statements will be monsters and very hard to write and very slow to perform.
Using the example I gave, if all employees are in one table and we want to know all of them hired since 1/1/1996, we just write:
select * from Employees where HireDate >= '1/1/1996'
Think about writing that same, simple statement with each employee instead stored in their own table! now, imagine writing a more complicated one, like average salary by department by year or something like that!
- Jeff |
Edited by - jsmith8858 on 10/03/2006 15:03:12 |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 10/03/2006 : 20:10:26
|
| jsmith, i undertand, i cant do anything about it. i didnt design it. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 10/03/2006 : 21:19:39
|
Then maybe it is time to re-design it. The time needed to fix what is there and then rewrite the exiting sql from scratch is going to be far less than writing or maintaining just a few basic sql statements with the current design.
You said you would speak with the guy who did design it -- did you do that yet? (be diplomatic, of course!)
- Jeff |
|
|
|
Topic |
|
table object is not showing.
