| Author |
Topic |
|
hennie7863
Starting Member
12 Posts |
 Posted - 2003-05-28 : 08:31:37
|
| Hi,I created a metatabel which describes tables which are dynamically created with Create table. When a field is added in this metatable the field should be added in the table. when this happens the column is added at the end of the table. This is not what i want. I want to add it on a certain position in the table. Before some meta fields.I know it's not possible to add a column with alter table. A option is to create temp table, copy data and rename the temp table. This is for me a difficult way to program (because of the metatable and the different ways the tables are created in our tool).Is het possible to split a table?Are there other ways to add a column not at the end?thxHennie |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-28 : 08:35:06
|
| Why are you adding COLUMNS to your metatable? You should be adding ROWS of data, not COLUMNS. SQL is designed to store/retrieve data dynamically in different rows, not columns.In your metatable, you should have a structure something like this:TableNameFieldNameattribute1attribute2..etc..(primary key of tablename/fieldname)Then you can have as many fields as you like, and also as many attributes as you like to describe that field. Don't add COLUMNS to your table as more data is accumulated, as more rows. Only add more columns if the TYPE of data you are accumulating CHANGES.I hope this makes some sense.- JeffEdited by - jsmith8858 on 05/28/2003 08:35:31 |
 |
|
|
hennie7863
Starting Member
12 Posts |
Posted - 2003-05-28 : 08:43:42
|
| Jeff,Thx for your comment. May be i wasn't clear. The tables which are described in the metatables are in rows (not columns off course)It's possible that the structure of the tables (which are described in the metatable) changes (new fields can be added). When this is so, the new column should be inserted in the same sequence as described in the metatable.....Hope this will clear some things...HennieEdited by - hennie7863 on 05/28/2003 08:44:21 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-05-28 : 09:41:58
|
| Basically, your the answer is no. You will have to copy your data out to a temp table, drop your base table, re-create your base table with the column where you want and then re-insert your data. That is how EM does it.It should be noted that a columns ordinal position is not considered in relational theory. The set {a,b,c} is equal to the set {b,a,c}. All information in a relational database is represented explicity in exactly one way - by values in tables - (Codd's...rip...Information Rule). By being critical of ordinal position you are saying there is information in said ordinal position. This is in violation of that rule....Yeah, but who was the Codd character and what the hell did he know anyway...theory is for the weak, I guess.Jay White{0} |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-28 : 09:54:28
|
| Jay -- One thing to keep in mind about the order of columns in tables, despite theory, is the fact that humans are looking at table structures, and it is a good idea to have them make logical sense.Even though {FirstName, MiddleName, LastName, Address1, Address2, City, State, ZIP}is the same as{Address2, MiddleName, ZIP, City, FirstName, State, Address1, LastName}you do have to recognize the fact that programmers are looking at data dictionaries or browsing through table columns and there is SOME merit in setting up tables with columns listed in some functional order. Especially if you do have tables with >10 columns.- JeffEdited by - jsmith8858 on 05/28/2003 09:55:22 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-05-28 : 10:04:04
|
| I hear ya, Jeff.I've got a certain 'handwriting' for when I do SQL code. I use lowercase for everything, no tabs rather 4 spaces, stuff like that. When developers don't write like me, I can still read it...Writing code that is dependent on ordinal position makes about as much sense as writing code thinks SELECT and select and Select are different.But, its Hennie's sandbox ... it doesn't have to make sense to me.Jay White{0} |
|
|
|
hennie7863
Starting Member
12 Posts |
Posted - 2003-05-28 : 11:43:42
|
| thx for the replys...The reason why i asked is this. At this moment we're creating a datawarehouse for helpdesksoftware. This means dimension tables needs to be created when reports needs information about this topic. These dimension tables will need some extra fields for administering the imported data. "Should i update it or create a new entry (record) for historical analysis". These fields are not from the meta table and i create them hardcoded. These fields are creating the problem. The new field should be inserted before the hardcoded fieldsThe inserts and updates are dynamically. This means that the metatable is the source for the whole dataware house. When a field is added at the end and the insert is assuming that this field is 2 fields earlier in the table than the insert is going wrong.Hennie |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-05-28 : 12:33:11
|
| Ummm...Sounds like your metadata is identifying the time of the load/insert?No?You can add a constraint and not have to worry about it.CREATE TABLE Table ( [some columns], [Created_Ts] [datetime] NOT NULL)GOALTER TABLE [dbo].[Company] WITH NOCHECK ADD CONSTRAINT [cons_name] DEFAULT (getdate()) FOR [Created_Ts]GOBrett8-) |
|
|
|
|
Adding columns in particular point in table