asked
Select or Insert from table dynamically
Hi there, I have a store proc that takes in an ID and depending on the ID it will select from the approperiate table. There is say 6 tables, all with the same data columns but broke out like this for performance as they contain tick data for different currency pairs. So I wanted to say something like Select * from Case MyTableId=1 Then EurUSDTicks Case MyTableId=2 Then GBPUSDTicks ... etc I didnt want to build a string an use the execute on it as I thought this would be possible to do in SQL. So the question is... is it possible and the second question is what is the best way to do this... I dont want to have an If statement on the Table ID and repeat the SQL with a different table name for all the tables. I have to do the same for the insert which I already coded but hate the code... its repeative and not cool IF (@currencyPairID = 1011) --EUR/USD INSERT INTO EURUSD (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@tickKey,@currencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@liquidityProviderID,@bankOrginatorID,NULL,@TickSourceID) ELSE IF (@currencyPairID = 1012) --GBP/USD INSERT INTO GBPUSD (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@tickKey,@currencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@liquidityProviderID,@bankOrginatorID,NULL,@TickSourceID) ELSE IF (@currencyPairID = 1013) --USD/JPY INSERT INTO USDJPY (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@tickKey,@currencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@liquidityProviderID,@bankOrginatorID,NULL,@TickSourceID) ELSE IF (@currencyPairID = 1014) --USD/CHF INSERT INTO USDCHF (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@tickKey,@currencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@liquidityProviderID,@bankOrginatorID,NULL,@TickSourceID) ELSE IF (@currencyPairID = 1015) --AUD/USD INSERT INTO AUDUSD (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@tickKey,@currencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@liquidityProviderID,@bankOrginatorID,NULL,@TickSourceID) ELSE IF (@currencyPairID = 1016) --USD/CAD INSERT INTO USDCAD (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@tickKey,@currencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@liquidityProviderID,@bankOrginatorID,NULL,@TickSourceID) ELSE IF (@currencyPairID = 1017) --NZD/USD INSERT INTO NZDUSD (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@tickKey,@currencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@liquidityProviderID,@bankOrginatorID,NULL,@TickSourceID) Thanks -M
To your first question, yes you can build a string , and it is relatively easy. You can read about a whole variety of techniques in the curse and blessing of dynamic SQL ( [
http://www.sommarskog.se/dynamic_sql.html][1] ) by Sommarskog. In your particular case, it might look something like: select @tablename = Case MyTableId=1 Then 'EurUSDTicks' `Case 'MyTableId=2' Then GBPUSDTicks ... etc set @sql = 'Select * from ' + @tablename However, as to the best way to do this, the short answer is you probably don't want to. Based on your description, you may be better off making a partitioned table (SQL Server 2005 or later) or a partitioned view. They were designed to handle exactly the situation you describe and while they add some complexity they are much cleaner from a theoretical standpoint and will likely work better than trying to do this all without them. You can read about partitioned tables at : [
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx][2] If you really want to do this dynamically, make sure you read through Sommarskog's article and understand the security risks and performance concerns involved. Sometimes dynamic SQL can be amazing and it is truly the only way to accomplish certain types of tasks. But do realize that unless you are very careful it runs the risk of opening up sql injection attacks and can limit query plan reuse. [1]:
http://www.sommarskog.se/dynamic_sql.html [2]:
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
@TimothyAWiseman gave you a good idea. For the insert, this can get you started. Of course, variables need to be defined properly, so check [BOL][1] for more info. -- Assign the table name SELECT @tablename = tablename from dbo.tablemap WHERE currencyPairID = @currencyPairID -- Construct Dynamic SQL SET @sql = ' INSERT INTO ' + @tablename + ' (TickKey,CurrencyPairID,Bid,Ask,QuoteDateTimeUTC,MarketMakerAgent,LiquidityProviderID,BankOrginatorID,LastAggregatedDate,TickSourceID) VALUES (@TickKey,@CurrencyPairID,@Bid,@Ask,@QuoteDateTimeUTC,@MarketMakerAgent,@LiquidityProviderID,@BankOrginatorID,@LastAggregatedDate,@TickSourceID) ' -- Execute EXEC sp_executesql @sql N'TickKey [datatype],CurrencyPairID [datatype],Bid [datatype], Ask [datatype],QuoteDateTimeUTC [datatype],MarketMakerAgent [datatype], LiquidityProviderID [datatype], BankOrginatorID [datatype], LastAggregatedDate [datatype], TickSourceID [datatype], @TickKey = @TickKey, @CurrencyPairID = @CurrencyPairID, @Bid = @Bid, @Ask = @Ask, @QuoteDateTimeUTC = @QuoteDateTimeUTC, @MarketMakerAgent = @MarketMakerAgent, @LiquidityProviderID = @LiquidityProviderID, @BankOrginatorID = @BankOrginatorID, @LastAggregatedDate = NULL, @TickSourceID = @TickSourceID; [1]:
http://msdn.microsoft.com/en-us/library/ms188001.aspx[datatype]
I would do something like this and resist the temptation to partition the table and insert everything into it. I have worked with currency data and partitioned tables, you will be happier with this approach EDIT: partitioned tables across multiple files/filegroups
@Timothy - great reply Timothy... made me read up on partioned tables and how they work and they seem to do exactly what I want but not sure on how they impact on performace so will have to set it up and test. I also see that its only available on 2008 dev or enterprise which is a pity but none the less I will try it out. @ozamora - great to hear that you have experience in the currency data so you will be aware of the amount of data that can hot a database in a second. I'm happy enough to code the sql dynamically and could follow what Tim was doing but not sure on your approach ozamora. Why have a variable assigned to itself... just havnt seen it built this way before... could you explain a little bit. Also did you find that the dynamic was faster than partitioned tables or what was you main reason for going this way. Would you have the currency tables like mine... ie a table for each currency pair.. thanks again.. Mick
If you have the opportunity to re architect then partitioned tables would help you by just inserting into 1 table and partitioning taking care of the segmentation; in this case having the currency pair as partitioned key. Now, based on my experience, partitioned tables are not faster that single tables. That is because the data inserted can point to any partition and the hard drive heads can bounce around for every record that needs to be inserted. If you are handling a high transactional system, you can insert into single tables and then ocne the data is ready for archival then switch into a partitioned table as an overnight process. Your question about variables to itself, sure you can and only in this particular scenario. Reason is because the engine understand that what is on the left side refers to the variable within the dyamic SQL and what is on the right refers to the variable within the stored proc scope. They do not mix as are executed in different contexts. Hope it helps.
ah top ozamora... ok so that really helped me make a decision. At this moment in time, I dont have the time to re-architect the design and yes the volumes I will have coming in could be around 200 ticks a second as I get them on the milli sec from various vendors. So dynamic it is. Then I noticed a suttle keyword in your example called sp_executesql ... and read up on it, so now I see what you are doing. Thanks again, really appreciated... @Tim... thanks Tim for the steer also... I read up on partitions and at least its another SQL piece of kit that I'm aware of and as for the link to the Dynamic SQL and injection attacks... well worth a read for an developer... Regards Mick
