March 26, 2012 at 5:08 am
Hi,
Is it possible to replicate data in a column and assign a "ReplicationID" for each incremental replication. eg.
I get the following list columns from an excel sheet that i bcp into a table with column named fields:
Actor
Arist
Director
Narrator
FilmStudio.
I would like to dynmically create tables for the above data but each with a different number of columns ie Actor/artist must have 6 columns, director must have 2 and narratot/filmStudio must have 1. I then include the ColumnNumber in the excel sheet. Actor, 6.. Artist, 6.. Director,2... etc and then i use the replicate statement and the associated number. It works but not THAT well. The output it give is obviously:
Table = ACTOR
Columns = ActorActorActorActorActorActor
The ideal output i am looking for is
Table = ACTOR
Columns =Actor1Actor2Actor3Actor4Actor5Actor6.
If you can help with this then i can clean it up to create the dynamic table.
PLEASE HELP!!!
Ismail
March 26, 2012 at 7:53 am
You don't really want to create tables like that, because it makes your data much harder to work with. Sometimes you may need to output data in that format from a stored procedure, but you shouldn't be storing your data in that format.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 27, 2012 at 1:30 am
Thanks for the info.
Could you tell me how it makes the data harder to work with? The reason i am trying to do something like this is because we receive a tab delimited txt file that includes pipe delimiters within certain fields. These pipe delimited columns will have certain validations on them in that a max of 6 names allowed in one column, two in another and so forth. Now i can target these columns so my thought was to create the actual columns which will serve as the validation. I will then import the file as is and create me a pipe delimited output that will be inserted into these temp tables after which i will be able to recreate the final output including validation.
I would like to hear what appraoch you may have. Thanks again for the response.
March 27, 2012 at 8:59 am
That subject is too long for a forum post, but you can search the web for "database normalization". Some quick points
* You have to search multiple fields instead of just one. So, you would have to search six actor fields to see if a particular actor is associated with your record.
* Deletions are harder. If you delete the second actor, you'll probably need to move the third actor to the second spot, the fourth to the third spot, etc.
If you want more help, you should follow the guidelines in Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply