Solved

VS 2008 Data Project. Create tables in different schema's

Posted on 2011-02-19
11
545 Views
Last Modified: 2013-11-26
Hi there,

I have a database project that creates tables. Some of the table structures are the same but are unique to a particular currency. So say I have a table called CurrencyQuote and I want one for each currency then what I done was create a Schema for each currency and then create that table in the schema.

However I want to Automate this from VS 2008 database project and dont want to create a table script for each currency when the only thing different is the schema that it goes into. Also if I want to add a column to the table I have to update all the curreny tables rather than just updaing the one table and then running it against the various schemas.

Any ideas on this ... thanks Mick
0
Comment
Question by:MickyMc
  • 3
  • 3
11 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
Comment Utility
Mick,

When you say you want the tables created in other schemas, I am guessing that your tables are normall created in the dbo schema and that you are doing either CREATE TABLE [dbo].TableName] or CREATE TABLE TableName].  In any case, all you really need to do is to specify the Schema, as in CREATE TABLE [schema].TableName].

Now, having said that, you also throw in the need to add a column to all instances of a given tablename.  Doing this dynamically is a very questionable practice (and is not included in the list of Best Practices ;-).  Therefore, I have to ask, "Why would you be needing to dynamically add a column to a table?"

I am also curious as to why you are wanting to create multiple CurrencyQoute tables.  Could you not just put a column for the CountryCode in a single CurrencyQuote table?  
0
 

Author Comment

by:MickyMc
Comment Utility
ah thanks 8080 for the reply. All your questions are totally valid and a few quick answers below for you.

I have 7 currencys which I recieve may 100 quotes a sec for... add this up over a period and the table gets big. So I,ve broken the table into 7 tables and used the Schema for each so I would have 7 schema..called EUR, GBP, USD  etc and a table called Currency quote in each.

I have 1 stored proc that is called and depending on the Currency passed in I update the table in that schema. When the tables get big, I aggregate the data up and delete some of the historial data. I do this to keep the tables light as I also need to read from them. This imho is faster than creating one big table.

Hope this explains a bit of the madness.

However I have a VS 200 database project and what I do is I have a schema script for each Currency. I then want to put the one currencyquote table, into each schema.. so really I want to loop for each schema and create a table in it.

Now all I want to know, is there a way to automate such a process in VS 2008 DB project. Instead I have 7 tables created for each schema... eg Create Table EUR.CurrencyQuote ... Create Table GBP.Currency quote.  If I want to add a column I need to change the 7 create scripts.

I hope this explains what I mean.

All the best and thanks Mick
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 500 total points
Comment Utility
Mick,

Why not set up a stored proc that you call, passing in the schema/currency name, in order to create the tables.  That way, you only have teh one place to do maintenance (the SP) and you can use it to create whatever currency tables you may need (for instance, if you ever need to handle Guatemalan Quetzals, you can do so ;-).

Since you are in SS2008, you could also partition the table by the currency flag, couldn't you? :-)
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:MickyMc
Comment Utility
emmh, kinda like the idea of using a sproc to create the tables 8080. I could also add it to a list of tables and inside my sprocs to get the data, I could check if the table was supported... Emmhh got me thinking 8080.

As for the partitions, at the time I read a fair bit on this approach and I was going to go for it, but there was a few gotcha's that I didnt like and cant remember... joys of getting old... I mean older :-)

Tick data is fairly intensive and this approach seemed to also be used by a few other people in the same game.

So all in all 8080, you dont think VS 2008 Db proj can handle this, which is my gut feeling also... but you have give me a viable solution.  If I dont hear another in the next day... points are yours for imagination :-)  thanks Mick
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Dhaest,

I don't know if you read the author's last post, but I think the author state a clear intention to award me the point:

"If I dont hear another in the next day... points are yours for imagination :-)  thanks Mick "

0
 

Author Comment

by:MickyMc
Comment Utility
Dhaest, I thought I had awarded the points to 8080 Diver, so just wondering why you are flagging this.

Sorry about that 8080 Diver... havnt changed my mind honest lol :-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now