What is the syntax for simultaneously updating two tables?
Coffeedrinker56
I've searched through the documentation and on the internet.
INSERT INTO target1, target2 ... doesn't do it!
aaron...@gmail.com
-Aaron
Douglas J. Steele
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Coffeedrinker56" <Coffeed...@discussions.microsoft.com> wrote in
message news:AD4E8A60-D3FC-49D1...@microsoft.com...
Jamie Collins
On Dec 13, 8:06 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
> > I'm attempting to update two referentially-dependenttables
> > simultaneously.
> > I've searched through the documentation and on the internet.
> > INSERT INTO target1, target2 ... doesn't do it!
>
> You can't. You must insert into one table, then the other.
Under some circumstances it may be achieved using a VIEW (Query
object).
Here's a very simple example where it works fine:
CREATE TABLE Table1 (
key_col INTEGER NOT NULL UNIQUE,
data_col INTEGER NOT NULL
)
;
CREATE TABLE Table2 (
key_col INTEGER NOT NULL UNIQUE
REFERENCES Table1 (key_col),
data_col INTEGER NOT NULL
)
;
CREATE VIEW View1
AS
SELECT T1.key_col, T1.data_col,
T2.key_col, T2.data_col
FROM Table1 AS T1
INNER JOIN Table2 AS T2
ON T1.key_col = T2.key_col
;
INSERT INTO View1
(T1.key_col, T1.data_col, T2.key_col, T2.data_col)
VALUES (1, 1, 1, 1)
;
Jamie.
--
'69 Camaro
> I'm attempting to update two referentially-dependent tables simultaneously.
> . . .
> INSERT INTO target1, target2 ... doesn't do it!
"INSERT INTO" appends (adds) new records, while update changes existing
records. From your question and subject line, I'm not sure whether you want
to update or add records.
If you want to add a record to both tables simultaneously, the trick is to
append one new record to a query that joins all the columns in both tables
(or at least the required columns). For this example, create tables on the
one side and the many side:
CREATE TABLE tblOneSide
(OID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY,
OneSideStuff Text (20) NOT NULL
CONSTRAINT One_IDX UNIQUE);
CREATE TABLE tblManySide
(MID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY,
OID Long NOT NULL,
CONSTRAINT OID_FK FOREIGN KEY (OID) REFERENCES tblOneSide,
ManySideStuff Text (20) NOT NULL
CONSTRAINT Many_IDX UNIQUE);
Next, create the query that joins the two tables and includes all columns in
both tables (or at least the required columns), and name it
qryBothOneSideAndManySide:
SELECT *
FROM tblOneSide INNER JOIN tblManySide
ON tblOneSide.OID = tblManySide.OID;
Now add a new record to this query:
INSERT INTO qryBothOneSideAndManySide (OneSideStuff, ManySideStuff)
VALUES ('111', 'mmm');
Voila! Two records inserted simultaneously into two tables with one INSERT
statement. Note that the same new AutoNumber for OID in tblOneSide will also
be added to the new record in the tblManySide table in the OID column. This
is why the INSERT statement only needed to include values for the required
columns in both tables.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
'69 Camaro
I might add that even if you don't use AutoNumber surrogate keys, this trick can
still be used, but a little extra effort is needed in the query that includes
the required columns.
CREATE TABLE tblOneSide
(OID Long
CONSTRAINT PrimaryKey PRIMARY KEY,
OneSideStuff Text (20) NOT NULL
CONSTRAINT One_IDX UNIQUE);
CREATE TABLE tblManySide
(MID Long
CONSTRAINT PrimaryKey PRIMARY KEY,
OID Long NOT NULL,
CONSTRAINT OID_FK FOREIGN KEY (OID) REFERENCES tblOneSide,
ManySideStuff Text (20) NOT NULL
CONSTRAINT Many_IDX UNIQUE);
The query can't use SELECT *, but must name all individual required columns. It
may be helpful to use aliases for the primary key/foreign key if they have the
same name in both tables. Name this new query qryAllReqdColsInBothTbls:
SELECT tblOneSide.OID AS OID_1, OneSideStuff,
MID, tblManySide.OID AS OID_M, ManySideStuff
FROM tblOneSide INNER JOIN tblManySide ON tblOneSide.OID = tblManySide.OID;
Now add a new record to this query using an INSERT INTO statement:
INSERT INTO qryAllReqdColsInBothTbls (OID_1, MID, OneSideStuff, ManySideStuff)
VALUES (11, 150, 'one', 'many');
Again note that the same new value for OID in tblOneSide will also be added to
the new record in tblManySide in the OID column.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
"'69 Camaro" <ForwardZERO_SP...@Spameater.orgZERO_SPAM> wrote in
message news:6A530303-9483-4B90...@microsoft.com...
'69 Camaro
I realize I forgot to address the other alternative of your question, updating
both tables simultaneously. Again, one needs the query joining both tables on
the foreign key, but the columns should be named, instead of using the SELECT *
syntax for all columns. For example, the query qryAllReqdColsInBothTbls listed
in my previous post:
SELECT tblOneSide.OID AS OID_1, OneSideStuff,
MID, tblManySide.OID AS OID_M, ManySideStuff
FROM tblOneSide INNER JOIN tblManySide ON tblOneSide.OID = tblManySide.OID;
To update both tables simultaneously, try:
UPDATE qryAllReqdColsInBothTbls
SET OneSideStuff = 'ddd', ManySideStuff = 'zzz'
WHERE OID_1 = 11;
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
"'69 Camaro" <ForwardZERO_SP...@Spameater.orgZERO_SPAM> wrote in
message news:%23TXRer8...@TK2MSFTNGP06.phx.gbl...
aaron...@gmail.com
you know any kb articles that discuss it in more detail?
i want more freedom to update and insert against views
or is this crap in an MDB?
-Aaron
Jamie Collins
'69 Camaro wrote:
> > SELECT *
> > FROM tblOneSide INNER JOIN tblManySide
> > ON tblOneSide.OID = tblManySide.OID;
> >
I feel I should point out that most professionals agree that using
SELECT * to return a resultset (rather than, say, in an EXISTS
subquery) has no place in production code.
I know you do not like to be contradicted <duck and g> but that you
enjoy showing me to be wrong (although your post seems to be an
expanded version of my CREATE VIEW example) so here's hoping you can
pull something peculiar to Access out of your hat to show that SELECT *
is a good thing.
Jamie.
--
'69 Camaro
> I feel I should point out that most professionals agree that using
> SELECT * to return a resultset (rather than, say, in an EXISTS
> subquery) has no place in production code.
Yet if any of those same professionals said, "You will never find a case where
SELECT * is acceptable outside of an EXISTS subquery in production code," we'd
probably take that with a grain of salt because there are usually exceptions to
every rule. I think this is one of them.
In my first post that listed two tables with AutoNumber surrogate keys, I used
the "SELECT *" syntax instead of:
SELECT tblOneSide.OID AS OID_1, OneSideStuff,
MID, tblManySide.OID AS OID_M, ManySideStuff . . .
. . . which I listed for the natural key example. Note that for the double
record entry trick to work, all required columns in both tables must receive a
value in the INSERT statement, either explicitly or implicitly (which happens
through the AutoNumber and foreign key constraint). There are no non-required
columns in either example table. Whether I list all five required columns or I
use the asterisk syntax for the column list, the query plan will retrieve the
same five columns in the two tables.
I'll let you compare query plans, query execution times, and network traffic to
see if explicitly listing all five columns in my SELECT query as the data source
for that INSERT statement has any advantage over my using a SELECT * query as
the data source for that same INSERT statement for the tables with surrogate
keys. (As I stated in the second post, the SELECT * syntax won't work for
non-surrogate key tables, so there's no need to compare two query plans for
tables with natural keys.)
> I know you do not like to be contradicted <duck and g>
If you see me post something that is incorrect or questionable, please speak up.
I'd much rather have an intelligent debate with someone who is articulate -- and
learn something new -- than to be called a jerk (and you know that's happened
before!) and have all discourse stop. On more than one occasion, I _knew_ you
were wrong . . . until I found out you were right, and I learned something new.
(But you're not going to get me to change my mind on Jet tables and clustered
indexes. Those aren't clustered indexes on the primary keys. We'll just have
to agree that we disagree on that.)
> you
> enjoy showing me to be wrong
Your code isn't wrong. I didn't want to embarrass you in front of God and
everyone, but folks E-mail me asking why you post SQL code that you haven't
tested first, because they get syntax errors when they attempt to run it in the
SQL View pane, and they ask me to fix your code. I explain that your code is
syntactically correct and describe what changes they need to make to get your
code to work for them, but they aren't willing to do so because of the unwanted
side effects on their other queries. My solution is to post an alternative that
works in their environments, too. It saves me time from having to E-mail the
same solution to several people, because they never ask all at once.
> so here's hoping you can
> pull something peculiar to Access out of your hat to show that SELECT *
> is a good thing.
In general, it isn't a good thing. However, in this case it was an either/or
and I picked the shorthand syntax for the first example, and offered a second
example without the shorthand syntax. For programming maintenance with this
trick, the SELECT * syntax offers some flexibility (although at a price for
performance) when there are non-required columns in the two tables. If only the
exact columns that were needed "this time" were listed in the join query,
multiple queries would usually need to be maintained to accommodate all possible
combinations of columns needed for the INSERT statements. Having one query with
all columns available for insertion can save time and prevent mistakes. But
again, that flexibility is only available to tables with AutoNumber surrogate
keys, not natural keys, and it comes at a cost of performance.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1166456766.2...@80g2000cwy.googlegroups.com...
Jamie Collins
'69 Camaro wrote:
> Your code isn't wrong. I didn't want to embarrass you in front of God and
> everyone, but folks E-mail me asking why you post SQL code that you haven't
> tested first, because they get syntax errors when they attempt to run it in the
> SQL View pane, and they ask me to fix your code. I explain that your code is
> syntactically correct and describe what changes they need to make to get your
> code to work for them, but they aren't willing to do so because of the unwanted
> side effects on their other queries. My solution is to post an alternative that
> works in their environments, too. It saves me time from having to E-mail the
> same solution to several people, because they never ask all at once.
Gunny,
I'm not embarrassed. I'd be flattered if I believed several people were
interested enough in my code to email you personally <g>.
> If you see me post something that is incorrect or questionable, please speak up.
And so, without further ado...<g>
> both tables must receive a
> value in the INSERT statement, either explicitly or implicitly (which happens
> through the AutoNumber and foreign key constraint).
The bit in parens is not quite right. The implicit behaviour has
nothing to do with the foreign key constraint; DROP the foreign key
constraint and the INSERT via the VIEW/Query still works. Rather, this
works because the two columns share the name 'OID': if I change the
name of tblManySide.OID to something else I get an error, 'Too few
parameters. Expected 1.'
Also, the autonumber ( 'surrogate'?) not really relevant either: change
it to a vanilla INTEGER column ('natural key'?) and supply a value for
OneSide.OID in the INSERT to the VIEW/Query and the 'coincidence of
name' behaviour remains the same.
Code to reproduce (tested in ANSI-92 Query Mode):
CREATE TABLE OneSide
(OID INTEGER NOT NULL
CONSTRAINT One_PrimaryKey PRIMARY KEY,
OneSideStuff VARCHAR(20) NOT NULL
CONSTRAINT One_IDX UNIQUE)
;
CREATE TABLE ManySide
(MID INTEGER IDENTITY(1, 1) NOT NULL
CONSTRAINT Many_PrimaryKey PRIMARY KEY,
OID INTEGER NOT NULL
CONSTRAINT OID_FK REFERENCES OneSide (OID),
ManySideStuff VARCHAR(20) NOT NULL
CONSTRAINT Many_IDX UNIQUE)
;
CREATE VIEW BothOneSideAndManySide
AS
SELECT *
FROM OneSide INNER JOIN ManySide
ON OneSide.OID = ManySide.OID
;
INSERT INTO BothOneSideAndManySide
(OneSide.OID, OneSideStuff, ManySideStuff)
VALUES (1, '111', 'mmm')
;
Voilà!
DELETE FROM ManySide
;
DELETE FROM OneSide
;
ALTER TABLE ManySide DROP
CONSTRAINT OID_FK
;
INSERT INTO BothOneSideAndManySide
(OneSide.OID, OneSideStuff, ManySideStuff)
VALUES (1, '111', 'mmm')
;
Et voilà!
So if your case for SELECT * is based on this 'coincidence of name'
behaviour then you've indeed pulled something Access-only out of the
hat and, even if I don't consider it a strong case, "Touché!" is due
:)
FWIW deleting via VIEW/Query doesn't work e.g.
DELETE FROM qryBothOneSideAndManySide
returns an error, 'Could not delete from specified tables.' :(
> Whether I list all five required columns or I
> use the asterisk syntax for the column list, the query plan will retrieve the
> same five columns in the two tables.
>
> I'll let you compare query plans, query execution times, and network traffic to
> see if explicitly listing all five columns in my SELECT query as the data source
> for that INSERT statement has any advantage over my using a SELECT * query
FWIW those things you mention are less important to me than issues of
SQL code 'maintenance'.
> create the query that joins the two tables and includes all columns in
> both tables (or at least the required columns)
>
> the INSERT statement only needed to include values for the required
> columns in both tables.
>
> all required columns in both tables must receive a
> value in the INSERT statement, either explicitly or implicitly
Just to clarify: your last statement conveys the correct point IMO.
When you say 'required' I assume you mean columns declared as NOT NULL
without a DEFAULT. In the Access user interface, NOT NULL is exposed as
'Required=Yes'. However, the term 'Required' is a bit misleading in
this context because a 'Required=Yes' column that also has a DEFAULT it
is not actually required e.g. for an INSERT; as you know, omitting a
column with a DEFAULT in an INSERT will implicitly use the DEFAULT
value.
Just another one of the many misleading terms used in the UI but I
thought it worthy of clarification.
> If you want to add a record to both tables simultaneously, the trick is to
> append one new record to a query that joins all the columns in both tables
> (or at least the required columns).
>
> Voila! Two records inserted simultaneously into two tables with one INSERT
> statement.
I don't think it is correct to say rows/records are added to both
tables simultaneously. Rather, the left-to-right order of tables is
significant. For example, with an INNER JOIN rows are added to the
leftmost table before rows are added to tables to the right.
In other words, the INSERT is not a single (atomic) action where the
rows are added simultaneously. Like the infamous 'clustered index'
issue, this would appear to be an example where in Access/Jet the
physical implementation shouldn't need to be considered but must for
logical code to work correctly and efficiently. I feel an example is
required...
Consider two tables identical in structure:
CREATE TABLE TableA (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL
)
;
CREATE TABLE TableB (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL
)
;
Further consider a constraint to ensure the data in TableA always
corresponds to the data in TableB:
ALTER TABLE TableA ADD
CONSTRAINT TableA_sync_TableB
CHECK(1 = (
SELECT COUNT(*)
FROM TableB AS B1
WHERE B1.key_col = TableA.key_col
AND B1.data_col = TableA.data_col
))
;
With this CHECK in place we need a way to INSERT to both tables, so we
can use the trick with a VIEW/Query to join the tables. Note the
left-to-right table order (TableB..JOIN..TableA):
CREATE VIEW TableBA
AS
SELECT A1.key_col AS key_col_A,
B1.key_col AS key_col_B,
A1.data_col AS data_col_A,
B1.data_col AS data_col_B
FROM TableB AS B1
INNER JOIN TableA AS A1
ON A1.key_col = B1.key_col
;
Inserting via the above VIEW/Query works fine:
INSERT INTO TableBA (key_col_A, key_col_B, data_col_A, data_col_B)
VALUES (1, 1, 2, 2)
;
Further consider a *logically* equivalent VIEW/Query where the
left-to-right table order is reversed (TableA..JOIN..TableB) but the
column order is the same:
CREATE VIEW TableAB
AS
SELECT A1.key_col AS key_col_A,
B1.key_col AS key_col_B,
A1.data_col AS data_col_A,
B1.data_col AS data_col_B
FROM TableA AS A1
INNER JOIN TableB AS B1
ON A1.key_col = B1.key_col
;
Inserting via this VIEW/Query fails e.g. (note the column order is
again the same):
INSERT INTO TableAB (key_col_A, key_col_B, data_col_A, data_col_B)
VALUES (55, 55, 99, 99)
;
generates the error, 'One or more values are prohibited by the
validation rule 'TableA_sync_TableB' set for 'TableA'. Enter a value
that the expression for this field can accept.'
The two VIEWs/Queries may be logically equivalent but they are treated
differently by the physical implementation of Access/Jet. According to
the SQL-92 standard to which Jet 'aspires', the CHECK should get
checked *after* the SQL statement has been executed. However, it is
clear that CHECKs are applied on a table-by-table basis determined by
left-to-right order in the VIEW/Query definition.
> (But you're not going to get me to change my mind on Jet tables and clustered
> indexes. Those aren't clustered indexes on the primary keys. We'll just have
> to agree that we disagree on that.)
I agree the term 'clustered index' is contentious but it is used in
relevant MSDN articles (e.g. "indices being stored in a clustered-index
format...the clustered index isn't maintained until the next compact...
The new clustered-key compact method is based on the primary key of the
table" from
http://support.microsoft.com/default.aspx?scid=kb;en-us;137039) and
there is no denying PRIMARY KEY designation affects physical ordering
on disk (as does a 'genuine' clustered index). Yes, I'll agree to
disagree but I remain in hope of something else for me in that hat of
yours... <g>
Jamie.
--
aaron...@gmail.com
there are a lot of times that using SELECT * would make things a lot
simpler.
I believe that usign SELECT * in a view is acceptable because it will
only use the columns that you query for.. RIGHT?
so it would make sense to use SELECT * in a view because it doesn't
have much extra cost; but it can cut down on maintenance a LOT.
I believe that MS has mis-diagnosed the database market.
I believe that they should embrace the concept of 'VWDB' just as much
as they do 'VLDB'
there should be support for large large large #s of columns in a
table--
and we should have the FREEDOM to be able to use statements like this
in order to see certain columnNames from a Select * statement
a) SELECT * {LIKE %ID} FROM myTable WHERE MyID = 115
this would give me a list of all of the columns from the table where
the column name ended in ID
b) SELECT * {ABC} FROM MyTable
this would give me all of the columns in a table, and the coulmns
would be arranged in alpabetical order isntead of in ordinal position
c) SELECT * {LIKE '%ID' ABC} FROM MyTable
this would give me all of the coulmns in a table where the column
name ended in ID.. and it would give me the column names in
alphabetical order
I just think that it's kinda ridiculous.. We don't NEED to have
cumbersome tools.. we should have MORE FLEXIBILITY than those damn
spreadsheet folk
If Microsoft were to embrace the VWDB market; and the other vendors
didn't-- I think that they would gain maybe 10 market share percentage
points?
It's an absolute no-brainer.
We don't need a new version of ADO.
we need a more dynamic, a more reasonable TSQL
-Aaron
Jamie Collins
aaron...@gmail.com wrote:
> there are a lot of times that using SELECT * would make things a lot
> simpler.
Indeed. Contrast 'simple' and 'intelligent' <g>.
> I believe that usign SELECT * in a view is acceptable because it will
> only use the columns that you query for.. RIGHT?
Did you test this is SQL Server?
CREATE TABLE OneSide (
OID INTEGER NOT NULL
CONSTRAINT one_PK PRIMARY KEY,
OneSideStuff VARCHAR(20) NOT NULL
CONSTRAINT One_IDX UNIQUE
)
;
CREATE TABLE ManySide (
MID INTEGER IDENTITY
CONSTRAINT many_PK PRIMARY KEY,
OID INTEGER NOT NULL
CONSTRAINT many_FK REFERENCES OneSide (OID),
ManySideStuff VARCHAR(20) NOT NULL
CONSTRAINT Many_IDX UNIQUE
)
;
CREATE VIEW BothOneSideAndManySide
AS
SELECT *
FROM OneSide INNER JOIN ManySide
ON OneSide.OID = ManySide.OID;
The CREATE VIEW statement generates an error, "Column names in each
view or function must be unique. Column name 'OID' in view or function
'BothOneSideAndManySide' is specified more than once."
It is *very* common for joined tables to have two (or more) columns of
the same name, even more often of same value (think equi-join) so there
is no need to expose both (or more) columns.
While we are on the subject of SQL Server, IMO the reasons for avoiding
SELECT * are even more compelling e.g. I saw the 'blob column must be
on the right' issue
(http://support.microsoft.com/default.aspx/kb/175239) crop up in QA
just last week.
> I believe that MS has mis-diagnosed the database market.
>
> I believe that they should embrace the concept of 'VWDB' just as much
> as they do 'VLDB'
Can you really mean, 'Very Wide Database'?!
Jamie.
--
Wayne-I-M
You need to put the question in the question section of the form or readers
will not be able to see it
--
Buon Natale, Happy Chritmas.
Wayne
Manchester, England.
Scusate,ma il mio Inglese fa schiffo :-)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.
"unknown" wrote:
>
aaron...@gmail.com
What do you think that I mean.. VolksWagen Database?
I disagree with your test case; it is irrelevent.
I would never so select *.. I would do select MyTable.*.. and then the
additional half dozen fields im looking for from the other table.
it saves a LOT of maintenance; and it doesn't cut down performance.
-Aaron
Jamie Collins
aaron...@gmail.com wrote:
> of [...] course I mean Very Wide Database.
>
> What do you think that I mean.. VolksWagen Database?
Well, my first though was Virtual Wargamers Discussion Board ("Real
gamers do it unplugged" <g>). A google for VWDB revealed, "Voidwars
Database Tool written for the purpose of getting rid of Excel/stuff"
(http://wiki.voidwars.com/wiki/VWDB) and knowing your views on Excel I
thought this could be the one <g>.
I don't even want to think why you need very wide tables when most of
us don't get much above 20 columns (every table in 1NF...?)
> I would never so select *.. I would do select MyTable.*.. and then the
> additional half dozen fields im looking for from the other table.
>
> it saves a LOT of maintenance; and it doesn't cut down performance.
VIEWs in the Microsoft SQL products are not performant period. In both
Jet and SQL Server VIEWs are not materialized (as they are in e.g.
Oracle); rather the VIEW definition is 'pasted' in as a derived table.
In other words, the advantage of using VIEWs in Microsoft SQL products
are for reasons 'logical' (make SQL code easier to write, restrict
access to certain data, reduce complexity, rename columns, etc) than
'physical' (performance, etc). So using derived tables will be more
performant than 'nested' VIEWs, especially in Jet, but doing so makes
SQL code harder to maintain.
I think the reason most professionals say *never* use SELECT * in
product code is that they've been tempted into doing so in the past and
have got burned - I know I have <g>!
Jamie.
--
aaron...@gmail.com
just because you say 'no professional would ever use Select *' I'm not
sure that I agree with you.
just because you say it; that doesn't make it true.
I believe that it should be easier to see a list of columns in
alphabetical order-- out of SQL Server-- I've struggled with this
before; it's not a design flaw on my part-- it's a simple fix that they
could implement and it wouldn't make anyones' queries RUN SLOWER
it just kinda bends the whole database design principles that you think
that you know and understand. those types of rules are what makes it
difficult for spreadsheet dorks to lose the trainig wheels and
understand databases.
data shouldn't have to 'change shape' when you move it from a
spreadsheet to a database.
minor modifications of course; but you shouldn't necessarily rewrite
the shape of the data every time you import a spreadsheet.
you and your 3rd normal CRAP-- and the lack of support for VWDB-- is
what makes Excel continue to be more popular than databases (while
databases are _OBVIOUSLY_ more powerful)
sometimes these rules are meant to be broken; and I believe that
Microsoft should provide support for VWDB - Very Wide Database on the
same footing as VLDB.
I've worked against probably 200 tables that are more than 100 columns.
that right there demonstrates that it's ridiculous to scroll through
column names, looking for the data that you need.
the whole world doesn't fit into 3rd normal form.
using calulcated totals-- storing calculated results-- is a necessity
in todays' day and age.
performant of views is NOT an issue. I don't use views to 'make things
go faster'
although they are nice because you can swap a temp table vs a view in
and out of a statement and compare / contrast.
you can't do the same thing with sprocs ROFL
views are simply an abstraction layer.
and sometimes-- it makes sense-- to be able to include 'all of the
columns in a table' in a view.
I dont' think that you should use Select * from Table1 inner join
Table2 on
but I dont think that there is a problem with Select Table1.*,
Table2.MyField1, Table2.MyField2 from Table1 inner join Table2
when I build cubes-- the ONLY OBJECTS I CAN USE are views
-Aaron
Jamie Collins
aaron...@gmail.com wrote:
> views are not all negative
>
> performant of views is NOT an issue. I don't use views to 'make things
> go faster'
>
> when I build cubes-- the ONLY OBJECTS I CAN USE are views
'Tis the season so I'll (selectively) agree with you there.
> just because you say 'no professional would ever use Select *' I'm not
> sure that I agree with you.
Do a google search for the exact phrase "never use select * in
production code". Those hits you see aren't all me <g>.
> I believe that it should be easier to see a list of columns in
> alphabetical order-- out of SQL Server-- I've struggled with this
> before; it's not a design flaw on my part-- it's a simple fix that they
> could implement and it wouldn't make anyones' queries RUN SLOWER
>
> it just kinda bends the whole database design principles that you think
> that you know and understand. those types of rules are what makes it
> difficult for spreadsheet dorks to lose the trainig wheels and
> understand databases.
I've only needed to do such tasks for applications that deal in
metadata i.e. database IDEs, management tools, etc.
I hope all your BLOB columns' names start with the letter 'z' <g>.
Jamie.
--
aaron...@gmail.com
the industry abhorance of Select * is what dooms RDBMS to obsolence and
guarantees that spreadsheets / XML will rule the world.
WE NEED SUPPORT FOR VWDB; it's not a joke; it's not a popular opinion.
But I should be able to search a single table; and get all of the
columns in alphabetical order.
it's a usability issue; not a database design issue.
-Aaron