|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
select variable in from statementI'm kinda new to SQL (Been working with access) and since links are not
permitted in SQL (except as a view with SELECT * FROM DATABASE.dbo.TABLENAME) it's a static connection where I need dynamic selection. What I would like to do is build a view to pull the DATABASE.dbo.TABLENAME from a reference table containing the info needed to complete the connection such as below. SELECT * FROM (SELECT fieldname_1 FROM tablewithfieldname_1 WHERE fieldname_2 = 1) This would give me the ability to select which of many tables with identacle formats, but with different data sets to update and/or edit records. Thought about using the union all statement, unfortunatly some of the tables would then have conflicting unique key entries and therefore would not be updateable. ANY SUGGESTIONS????? > ANY SUGGESTIONS????? Yes. Redesign.Why segment identical data across lots of different tables? This is an inherently weak design because it represents data as metadata and you'll probably be forced to use lots of complex and inefficient dynamic SQL. This is not the way to build an effective database. > Thought That's because you are missing one element of data from your tables. Namely, > about using the union all statement, unfortunatly some of the tables would > then have conflicting unique key entries and therefore would not be > updateable. the piece of data that you are representing using separate tables - probably you've used it as the table names. Add that extra attribute to the key and you have a single table or a UNION. Assuming you are forced to use this design, fix the key issue and then create a partitioned view (UNION) across all the tables. Take a look at Partitioned Views in Books Online. -- David Portas SQL Server MVP -- The data is not identical, the format of the data and the tables are. Each
table represents a different customer base for each of our clients. We process the customer interaction the same way for each but have different "campaigns" for each, so different tables to keep the customer base separate. Each of the tables have 3 fields in a unique clustered PK index. Would adding another field , say the "campaignid" field to that index make it more unique? And what would the cost be to the server. We sometimes "update" as many as 10,000 or more records a day. Table size runs anywhere from 50,000 to 3 million records. The tables are in a different database, but physically on the same server. I'm trying to build a reporting database that would have a different set of tables and data but would be able to interact with the tables in the main database. Show quote "David Portas" wrote: > > ANY SUGGESTIONS????? > > Yes. Redesign. > > Why segment identical data across lots of different tables? This is an > inherently weak design because it represents data as metadata and you'll > probably be forced to use lots of complex and inefficient dynamic SQL. This > is not the way to build an effective database. > > > Thought > > about using the union all statement, unfortunatly some of the tables would > > then have conflicting unique key entries and therefore would not be > > updateable. > > That's because you are missing one element of data from your tables. Namely, > the piece of data that you are representing using separate tables - probably > you've used it as the table names. Add that extra attribute to the key and > you have a single table or a UNION. > > Assuming you are forced to use this design, fix the key issue and then > create a partitioned view (UNION) across all the tables. Take a look at > Partitioned Views in Books Online. > > -- > David Portas > SQL Server MVP > -- > > > You haven't said anything that significantly alters my previous opinion.
Yes, the extra attribute to the compound primary key and either combine the data in one table or in a partitioned view. A partitioned view requires a key that's unique across all the tables - that's the reason you got this same suggestion from John as well. The only thing I'll add is that if the rate of updates or growth is as low as 10,000 rows per day then I would probably incline towards using a single table rather than a partitioned view. If you do partition, then consider doing so by date range rather than by some business attribute like "campaign" and split the base tables across different physical devices. That way updates will hit only one table but queries can benefit from parallelism across many. -- David Portas SQL Server MVP -- Hi
Why not add a partitioning column to your tables so that there is effectively a composite primary key? John Show quote "RWG" <R**@discussions.microsoft.com> wrote in message news:2F27825E-1468-4C5D-817B-6ABE2F27CEF1@microsoft.com... > I'm kinda new to SQL (Been working with access) and since links are not > permitted in SQL (except as a view with SELECT * FROM > DATABASE.dbo.TABLENAME) > it's a static connection where I need dynamic selection. What I would > like > to do is build a view to pull the DATABASE.dbo.TABLENAME from a reference > table containing the info needed to complete the connection such as below. > > SELECT * > FROM (SELECT fieldname_1 > FROM tablewithfieldname_1 > WHERE fieldname_2 = 1) > > This would give me the ability to select which of many tables with > identacle > formats, but with different data sets to update and/or edit records. > Thought > about using the union all statement, unfortunatly some of the tables would > then have conflicting unique key entries and therefore would not be > updateable. > > ANY SUGGESTIONS????? How would I add a partitioning column. The current PK has unique and
clustered on 3 fields. Those three fields data can be duplicated between some of the tables. Do I add a new field, and if so, how do I keep the values unique? Show quote "John Bell" wrote: > Hi > > Why not add a partitioning column to your tables so that there is > effectively a composite primary key? > > John > > "RWG" <R**@discussions.microsoft.com> wrote in message > news:2F27825E-1468-4C5D-817B-6ABE2F27CEF1@microsoft.com... > > I'm kinda new to SQL (Been working with access) and since links are not > > permitted in SQL (except as a view with SELECT * FROM > > DATABASE.dbo.TABLENAME) > > it's a static connection where I need dynamic selection. What I would > > like > > to do is build a view to pull the DATABASE.dbo.TABLENAME from a reference > > table containing the info needed to complete the connection such as below. > > > > SELECT * > > FROM (SELECT fieldname_1 > > FROM tablewithfieldname_1 > > WHERE fieldname_2 = 1) > > > > This would give me the ability to select which of many tables with > > identacle > > formats, but with different data sets to update and/or edit records. > > Thought > > about using the union all statement, unfortunatly some of the tables would > > then have conflicting unique key entries and therefore would not be > > updateable. > > > > ANY SUGGESTIONS????? > > > Hi
You unique key will still be unique as the partitioning column would be constant for any given table you could then use a partitioned view. John Show quote "RWG" <R**@discussions.microsoft.com> wrote in message news:7FACA566-F9DC-4798-BD4C-1CDD60BCFAF0@microsoft.com... > How would I add a partitioning column. The current PK has unique and > clustered on 3 fields. Those three fields data can be duplicated between > some of the tables. Do I add a new field, and if so, how do I keep the > values unique? > > "John Bell" wrote: > >> Hi >> >> Why not add a partitioning column to your tables so that there is >> effectively a composite primary key? >> >> John >> >> "RWG" <R**@discussions.microsoft.com> wrote in message >> news:2F27825E-1468-4C5D-817B-6ABE2F27CEF1@microsoft.com... >> > I'm kinda new to SQL (Been working with access) and since links are not >> > permitted in SQL (except as a view with SELECT * FROM >> > DATABASE.dbo.TABLENAME) >> > it's a static connection where I need dynamic selection. What I would >> > like >> > to do is build a view to pull the DATABASE.dbo.TABLENAME from a >> > reference >> > table containing the info needed to complete the connection such as >> > below. >> > >> > SELECT * >> > FROM (SELECT fieldname_1 >> > FROM tablewithfieldname_1 >> > WHERE fieldname_2 = 1) >> > >> > This would give me the ability to select which of many tables with >> > identacle >> > formats, but with different data sets to update and/or edit records. >> > Thought >> > about using the union all statement, unfortunatly some of the tables >> > would >> > then have conflicting unique key entries and therefore would not be >> > updateable. >> > >> > ANY SUGGESTIONS????? >> >> >> The three fields that are part of the PK are: CountryCode (1 for USA),
TelephoneNumber, and UniqueID (which is unpopulated). Telephone number is not duplicated in the individual tables, however, it does have the same phone number in multiple tables. All three are char fields and never change, there are other fields that have data needing updating. Query analyzer is not automatically creating a "partition" for the view and will not let me update any individual record that I may call for in the view. Says it cannot find or create the partitioning column. Do I need to add a field to the table or how do i define the partitioning column? Show quote "John Bell" wrote: > Hi > > You unique key will still be unique as the partitioning column would be > constant for any given table you could then use a partitioned view. > > John > > "RWG" <R**@discussions.microsoft.com> wrote in message > news:7FACA566-F9DC-4798-BD4C-1CDD60BCFAF0@microsoft.com... > > How would I add a partitioning column. The current PK has unique and > > clustered on 3 fields. Those three fields data can be duplicated between > > some of the tables. Do I add a new field, and if so, how do I keep the > > values unique? > > > > "John Bell" wrote: > > > >> Hi > >> > >> Why not add a partitioning column to your tables so that there is > >> effectively a composite primary key? > >> > >> John > >> > >> "RWG" <R**@discussions.microsoft.com> wrote in message > >> news:2F27825E-1468-4C5D-817B-6ABE2F27CEF1@microsoft.com... > >> > I'm kinda new to SQL (Been working with access) and since links are not > >> > permitted in SQL (except as a view with SELECT * FROM > >> > DATABASE.dbo.TABLENAME) > >> > it's a static connection where I need dynamic selection. What I would > >> > like > >> > to do is build a view to pull the DATABASE.dbo.TABLENAME from a > >> > reference > >> > table containing the info needed to complete the connection such as > >> > below. > >> > > >> > SELECT * > >> > FROM (SELECT fieldname_1 > >> > FROM tablewithfieldname_1 > >> > WHERE fieldname_2 = 1) > >> > > >> > This would give me the ability to select which of many tables with > >> > identacle > >> > formats, but with different data sets to update and/or edit records. > >> > Thought > >> > about using the union all statement, unfortunatly some of the tables > >> > would > >> > then have conflicting unique key entries and therefore would not be > >> > updateable. > >> > > >> > ANY SUGGESTIONS????? > >> > >> > >> > > > You can set up a partitioned view like this. Notice the CHECK
constraints used to define the partitioning column. See Books Online for more details. CREATE TABLE table1 (partition_col INTEGER NOT NULL CHECK (partition_col = 1), countrycode CHAR(3) NOT NULL /* REFERENCES countries (countrycode) */, telephone_num CHAR(15) NOT NULL, uniqueid CHAR(1) NOT NULL /* ?!! */, PRIMARY KEY (partition_col, countrycode, telephone_num, uniqueid)) ; CREATE TABLE table2 (partition_col INTEGER NOT NULL CHECK (partition_col = 2), countrycode CHAR(3) NOT NULL /* REFERENCES countries (countrycode) */, telephone_num CHAR(15) NOT NULL, uniqueid CHAR(1) NOT NULL /* ?!! */, PRIMARY KEY (partition_col, countrycode, telephone_num, uniqueid)) ; CREATE TABLE table3 (partition_col INTEGER NOT NULL CHECK (partition_col = 3), countrycode CHAR(3) NOT NULL /* REFERENCES countries (countrycode) */, telephone_num CHAR(15) NOT NULL, uniqueid CHAR(1) NOT NULL /* ?!! */, PRIMARY KEY (partition_col, countrycode, telephone_num, uniqueid)) ; GO CREATE VIEW dbo.foo AS SELECT partition_col, countrycode, telephone_num, uniqueid FROM dbo.table1 UNION ALL SELECT partition_col, countrycode, telephone_num, uniqueid FROM dbo.table2 UNION ALL SELECT partition_col, countrycode, telephone_num, uniqueid FROM dbo.table3 GO In Enterprise Edition this view is updateable, in Standard Edition read-only. You mentioned elsewhere in the thread that you only need to access one database at a time. The easiest way to do that is through a dynamic connection string in your application or middle-tier. Try to avoid building dynamic code in your database (Jeremy Williams gave you an example). Dynamic code is hard work to maintain and support and it usually has adverse implications for performance and security. -- David Portas SQL Server MVP -- Please post DDL, sample data, and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006) OK - the "right" way would be to have one table to pull from, not multiple tables with identical formats (called schema). Since you did not provide any actual examples of structure, I am going to guess that each table in question holds the exact same type of information, but for a different time frame or for different categories (i.e. departments, products, etc.). This is called attribute-splitting, and is generally considered a bad practice (watch for wrapping): http://groups.google.com/groups?as_q=attribute+splitting&as_ugroup=microsoft.public.sqlserver.* If this is done for performance reasons, there are other ways to address those concerns that do not involve doing breaking the table up. If you have so many rows that performance is unacceptable without this technique, you might want to look into archiving the data, or moving over to a VLDB system (although, if this database is a port from Access, it is unlikely that it holds too many rows for adequate performance on SQL Server). An alternate approach is what some around here would term the "shoot yourself in the foot" technique. This involves dynamic SQL. You could try creating a stored procedure like this: ------ CREATE PROCEDURE RetrieveAnyData @TableName sysname AS DECLARE @BadSQL varchar(2000) SET @BadSQL = 'SELECT fieldname_1 FROM ' + @TableName + ' WHERE fieldname_2 = 1' EXEC(@BadSQL) ------ Then you could pass in the table name like this when you needed data from any of the tables: EXEC RetrieveAnyData 'BadTable' Of course, this is a fairly large security problem (google for 'SQL Injection'), but it might get you by. Be sure to consult http://www.sommarskog.se/dynamic_sql.html for more information on this type of technique. Show quote "RWG" <R**@discussions.microsoft.com> wrote in message news:2F27825E-1468-4C5D-817B-6ABE2F27CEF1@microsoft.com... > I'm kinda new to SQL (Been working with access) and since links are not > permitted in SQL (except as a view with SELECT * FROM > DATABASE.dbo.TABLENAME) > it's a static connection where I need dynamic selection. What I would > like > to do is build a view to pull the DATABASE.dbo.TABLENAME from a reference > table containing the info needed to complete the connection such as below. > > SELECT * > FROM (SELECT fieldname_1 > FROM tablewithfieldname_1 > WHERE fieldname_2 = 1) > > This would give me the ability to select which of many tables with > identacle > formats, but with different data sets to update and/or edit records. > Thought > about using the union all statement, unfortunatly some of the tables would > then have conflicting unique key entries and therefore would not be > updateable. > > ANY SUGGESTIONS????? Jeremy
Sorry about that. Wasn't sure what to send. The process I'm trying to recreate from my access application is where I link a table with a standard name like "CAMPAIGN", then query that table via a form, edit the data, then exit the form. I have another table "CAMPAIGNLOCATION" that lists the locations of said tables via their respective databases (CampaignID -nvarchar.3, CampaignName -nvarchar.50, CampaignLocal - nvarchar.255, CampSelect -bit.1). When I first make the database selection, the process would delete the table link called "CAMPAIGN" and then link the selected table from the selected database as "CAMPAIGN". What I'm looking for is a way to do the same inside a SQL database. That way I can use "CAMPAIGNLOCATION" criteria to create a view of the tables one at a time for editing customer records. An individual selection if I manually write the view would read as SELECT * FROM DATABASENAME.dbo.CustomerTable_1 since the table I'm looking at is in a different database. What I'd like to replace the DATABASENAME.dbo.CustomerTable_1 with is the value in CAMAPIGNLOCAL.CAMPAIGNLOCATION where CAMPSELECT is true (= 1), so that I can edit the customers record in the appropriate table. I know (and if I had designed the database with the customer data, I would have done it the described way with just one table) it isn't as easy to do or maybe I need more instruction. Unfortunately, I tried to get it to create a partitioning view using the UNION ALL statement, unfortuneatly there is not any single field in any of those tables that would be unique when joined and I don't have a clue as to how to do that with millions of records across a dozen or more tables. What I hope to be able to do is have a single place in the new SQL database that would allow me to build other views, forms, and reports where the only thing that changes in that place is where it pulls its data, whether it be "DATABASENAME.dbo.CustomerTable_1" or "DATABASENAME.dbo.CustomerTable_2" or "DATABASENAME.dbo.CustomerTable_3" etc. Since they all have the same format, but different data sets and I don't want to have to create the same form and do the same functions for each instance of the customer table, I'd like to be able to (for lack of a better word) dynamically select the data. Is it possible or would it be a nightmare. Show quote "Jeremy Williams" wrote: > Please post DDL, sample data, and expected results > (http://www.aspfaq.com/etiquette.asp?id=5006) > > OK - the "right" way would be to have one table to pull from, not multiple > tables with identical formats (called schema). Since you did not provide any > actual examples of structure, I am going to guess that each table in > question holds the exact same type of information, but for a different time > frame or for different categories (i.e. departments, products, etc.). This > is called attribute-splitting, and is generally considered a bad practice > (watch for wrapping): > http://groups.google.com/groups?as_q=attribute+splitting&as_ugroup=microsoft.public.sqlserver.* > > If this is done for performance reasons, there are other ways to address > those concerns that do not involve doing breaking the table up. If you have > so many rows that performance is unacceptable without this technique, you > might want to look into archiving the data, or moving over to a VLDB system > (although, if this database is a port from Access, it is unlikely that it > holds too many rows for adequate performance on SQL Server). > > An alternate approach is what some around here would term the "shoot > yourself in the foot" technique. This involves dynamic SQL. You could try > creating a stored procedure like this: > > ------ > CREATE PROCEDURE RetrieveAnyData @TableName sysname AS > > DECLARE @BadSQL varchar(2000) > SET @BadSQL = 'SELECT fieldname_1 FROM ' + @TableName + ' WHERE fieldname_2 > = 1' > EXEC(@BadSQL) > ------ > > Then you could pass in the table name like this when you needed data from > any of the tables: > EXEC RetrieveAnyData 'BadTable' > > Of course, this is a fairly large security problem (google for 'SQL > Injection'), but it might get you by. Be sure to consult > http://www.sommarskog.se/dynamic_sql.html for more information on this type > of technique. > > "RWG" <R**@discussions.microsoft.com> wrote in message > news:2F27825E-1468-4C5D-817B-6ABE2F27CEF1@microsoft.com... > > I'm kinda new to SQL (Been working with access) and since links are not > > permitted in SQL (except as a view with SELECT * FROM > > DATABASE.dbo.TABLENAME) > > it's a static connection where I need dynamic selection. What I would > > like > > to do is build a view to pull the DATABASE.dbo.TABLENAME from a reference > > table containing the info needed to complete the connection such as below. > > > > SELECT * > > FROM (SELECT fieldname_1 > > FROM tablewithfieldname_1 > > WHERE fieldname_2 = 1) > > > > This would give me the ability to select which of many tables with > > identacle > > formats, but with different data sets to update and/or edit records. > > Thought > > about using the union all statement, unfortunatly some of the tables would > > then have conflicting unique key entries and therefore would not be > > updateable. > > > > ANY SUGGESTIONS????? > > >  |
|||||||||||||||||||||||
Other interesting topics