I have tried two ways of and these are the results. The 1st technique
is close
select
1 as Tag,
null as Parent,
'' as [table!1],
null as [field!2!table-name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1],
i.TABLE_NAME as [field!2!table-name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
select
1 as Tag,
null as Parent,
i.TABLE_NAME as [table!1!name],
null as [field!2!name],
null as [field!2!type],
null as [field!2!size]
from
information_schema.columns i
UNION ALL
select
2 as Tag,
1 as Parent,
null as [table!1!name],
i.COLUMN_NAME as [field!2!name],
i.DATA_TYPE as [field!2!type],
i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]
from
information_schema.columns i
where
table_name = 'MtFeedback'
FOR XML EXPLICIT
I have an allergy table which has a patientid and an allergy id. i would like to create a view(or SQL statement) that will give me a crosstab of a patient and there allergies(like below)
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
I'm working on a couple projects and I've recently been trying to make everything fully normalized so updates are easier and I'm just wondering if there's a standard way to query and update normalized tables. For example: If I have table People with columns ID, FirstName, LastName, Height, Weight, ShoeSize, I can normalize that into two tables. Table People has ID, FirstName and LastName. Table PeopleDetails has PeopleID (FK), Property and Value. That way i can add more properties later right at the presentation layer if I like. Essentially I moved the data from being horizontal to being vertical. But doing a simple search for people means I have to search the details table and return a LOT more records (one each for Height, Weight and ShoeSize) not to mention any more details I might add later. With a lot of details, it seems like your performance would take a big hit and your code would get really complicated as your looping through a vertical dataset to find the properties you want. Or is there some other standard way of doing that? I'm just hoping that someone else has solved these problems and there's a standard set of functions out there for selecting and updating this kind of DB structure. Anyone?
Hi. I have a project I need to complete and I really don't know the first step I should take. Basically, we have a case management system that is normalized for the most part except for one major flaw: the clients table. Whenever we add a new case, we have to add the customer's name/address/phone etc. all over again. I would like to redo this current setup so we have one table just for clients and another table just for cases, so we don't have all this double entering all the time. Is there an easy way to do this or could someone point me in the right direction? It's on a SQL Server 2000 database. Thanks for your help!
Dear Experts,When I use a single table I can easily use constraints to enforce mybusiness logic, but what do I do when I normalize a single table intomultiple tables.For example, imagine that my initial table has the columns ID, Name,Salary with the constraint that Salary is not NULL. Now imagine thatI break this into two tables, one with ID and Name and another with IDand Salary. I would like to have a constraint that prevents thecreation of a row with (ID,Name) in the first table unless acorresponding row in the second table is also created.I can enforce this logic with triggers, but it looks ugly and isfairly brittle. Is there a better way or is this the dark side ofnormalization?Thanks.
Hi I am new to SSIS. Probaly What I am asking is the simple one. But I dont know. I have a requirement. My Data source is Excel file and Destination is SQL Server . Excel file consist around 10k unnormalized rows which should be loaded to the multiple normalized master and child tables into the destination with the Primary key (identity column) and foreign key constrains. Even sometimes package should check for destination database table for matching Source column (Excel) code with the lookup table code (destination lookup table) and get the Primary key of Lookup table and put it into the destination table. Please help me the way to find the solution for this
I'm working with a database that is poorly normalized and am doing my best to increase the speed of our queries (changing the database itself is not an option right now). Any help with the following situation would be very much appreciated.
In the DataTable, the columns named ColA, ColB, ColC, etc., are described in the NameTable where Abbr would be A, B, C, etc., and FullName would be a "nice" text version describing what's stored in that column. For instance, a record in the NameTable might include Abbr = 'A' and FullName = 'Computer Science', and that means that ColA in the DataTable refers to 'ComputerScience'.
The table I'm trying to optimize the creation of in our ASP.NET application needs to have column headings that could be retrieved like this: SELECT DISTINCT CodeB, CodeC FROM DataTable WHERE CodeA = @CodeA (Note that CodeC is really just a different representation of CodeB--think 4-digit year vs. 2-digit year--for any given CodeA.)
The row headings for the table could be retrieved like this: SELECT FullName FROM NameTable
The cells of the table at any given intersection of a row heading and a column heading can be retrieved like this: SELECT Col? FROM DataTable WHERE CodeA = @CodeA AND CodeB = @CodeB (this selects a single value) Where the ? of Col? is determined by the Abbr version of FullName shown in the row heading, and @CodeB is determined by CodeB from the column heading.
Right now this table is generated using a lot of loops in the code of the page that require it to query the database for nearly every cell in the table. Even with this poor database design, there has to be a better way than that. I'm hoping there is a way to create a stored procedure that uses temporary tables and joins, or views, or something like that to create the table on the SQL Server and just send the data to the ASP.NET page in a form that can be directly databound to a GridView.
Ok, I'm fairly new to .NET and even newer to the whole database concept. But, don't run away yet, I'm no idiot and I shouldn't have too hard of a time understanding your responses if you're kind of enough to give them. That being said, here's my dilemma:I'm trying to make a database of all the movies I own, the actors in them and the genre (s) they belong to. I have a set of tables that are in the 2NF (I think). I have a movies table, an actors table, a genres table, and two tables called movies_actors and movies_genres with primary-foreign key relationships to pull it all together (e.g. movie_id 1 has two entries in movies_genres, one for Action and one for Drama). My problem arises that when execute my monster query to pull ALL the data on one movie, I get a row returned for every combination of Genres and Actors in a movie. Example:movie_id movie_title comments actor_first actor_last genre_name1 Casino blah blah Robert DeNiro Action1 Casino blah blah Robert DeNiro Drama1 Casino blah blah Joe Pesci Action1 Casino blah blah Joe Pesci Drama And here's the query that produced that:1 SELECT movies.movie_title, movies.comments, actors.actor_first, 2 actors.actor_last, genres.genre_name 3 FROM movies INNER JOIN movies_actors ON movies.movie_id = movies_actors.movie_id 4 INNER JOIN actors ON movies_actors.actor_id = actors.actor_id 5 INNER JOIN movies_genres ON movies_genres.movie_id = movies.movie_id 6 INNER JOIN genres ON movies_genres.genre_id = genres.genre_id So, I want to put all the actors for one movie into the same cell in the datagrid (same with the genres) and still keep it sortable by actor or genre. Is this possible with the .NET 2.0 datagrid? Do I have some fundamental misunderstanding of how my tables should be structured? Am I just really far off and acting like a n00b?
First of all I think of myself as a software developer and by no means a database expert. So I am posting a question here hoping that someone will lead me in the right direction.
I am somewhat following the database diagram from AdventureWorks with my own "normalized" database and have a question on how to update the table(s).
Here is my situation: Table: Client ClientID (PK) Name ...
Table: Address AddressID (PK) AddressLine1 AddressLine2 City ...
The tables should be referenced properly, etc... So my question is, when I add a new Client record, how and when do I update the Client table, Address table and ClientAddress table? If my PK on the Client and Address tables is an autoincriment, do I need it before I can update the ClientAddress table? If so, what do I have to look for, or how do I get the newly created ID to update the ClientAddress?
Is this making any sense? - I'm a little lost/confused. So if anyone can provide me links to where I can learn more about stuff like this and/or post some ideas/solutions, I would greatly appreciate it. Also, if I am not clear on my issue, please ask and I will see if I can clarify it more.
I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way.
I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later.
Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables.
I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc.
I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables.
Any comments? Suggestions? All is welcome.
I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. As are suggestions for further reading.
Thanks again...
simplified example (my denormalized tables are 20 - 30 colums wide)
denormalized table: =================== name, address, city, state, cellphone, homephone
I'm breaking up the denormalized tables like this (*UNTESTED*): =================================================
DECLARE @vars.... (one for each column in my normalized table structure, matching size and type)
DECLARE myCursor CURSOR FAST_FORWARD FOR SELECT name, address, city, state, cellphone, homephone FROM _DNT_myWideTable INTO
WHILE @@Fetch_Status = 0 BEGIN -- grab the next row from the wide table FETCH NEXT FROM myCursor INTO @name, @address, @city, @state, @cellphone, @homephone
-- create the person first and get the ID with @@IDENTITY INSERT INTO tblPerson (name) VALUES (@name)
SET @personID = @@IDENTITY
-- use that ID to coordinate inserts across other tables INSERT INTO tblAddress (FK_person, address, city, state, addressType) VALUES(@person, @address, @city, @state, 'HOME')
INSERT INTO tblContact (FK_person, data, contactType) VALUES(@person, @cellphone, 'CELLPHONE')
INSERT INTO tblContact (FK_person, data, contactType) VALUES(@person, @homephone, 'HOMEPHONE')
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
Sample Data would be 1 | CRYS | CRYSTAL STAIRS 2 | MAOF | MEXICAN AMERICAN FOUNDATION 3 | PATH | PATHWAYS 4 | CCRC | CHILD CARE RESOURCE CENTER 5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA ==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused: (1) Is the use of FK as a Constraint any different than using an INDEX and how? (2) Should I Alter the Tables to include CASCADING Up/Down? (3) Are the use of CHARs Ok for the Keys? (4) Have I over/under-normalized any of the relationships?
I have a "source" table that is being populated by a DTS bulk importof a text file. I need to scrub the source table after the importstep by running appropriate stored proc(s) to copy the source data to2 normalized tables. The problem is that table "Companies" needs tobe populated first in order to generate the Identity ID and then usethat as the foreign key in the other table.Here is the DDL:CREATE TABLE [dbo].[OriginalList] ([FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Addr1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Companies] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[CompanyLocations] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[CompanyID] [int] NOT NULL ,[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThis is the stored proc I have at this time that does NOT work. Ituses the last Company insert for all the CompanyLocations which is notcorrect.CREATE PROCEDURE DataScrubSP ASBegin Transactioninsert Companies (Name) select Company from OriginalListIF @@Error <> 0GOTO ErrorHandlerdeclare @COID intselect @COID=@@identityinsert CompanyLocations (CompanyID, Addr1, City, State, Zip) select@COID, Addr1, City, State, Zip from OriginalListIF @@Error <> 0GOTO ErrorHandlerCOMMIT TRANSACTIONErrorHandler:IF @@TRANCOUNT > 0ROLLBACK TRANSACTIONRETURNGOThanks for any help.Alex.
I am pretty new to SSIS, so please excuse me if this is a trivial question.
I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.
I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?
I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.
The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!
There will only be a max of 3 Country entries for each Employee. So I want to select the EmployeeId and get the three CountryIds so it would look like this:
I normalized the below tables but I am finding it difficult to copy data to the new tables. How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it. CREATE TABLE SKU_DATA ( SKU Integer NOT NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer). SKU and SKU_Description are candidate keys. Primary key is SKU.
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
I've set up a website on another computer using sql server express and visual web developer express, and it works as expected. However halfway through the development i've had to move to another computer. I installed visual web developer express + sql server express, and copied the project files across, but now when I attempt to run it I get the error:Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. I've tried reinstalling the software and it doesn't help. Any idea why this is happening, and what I can do to fix it?thanks
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
hi there. i'm using asp.net 2 page i'm accessing a table consists of 100 thousands rows and its slow and i'm wondering instead of accessing directly to the table how about if i access via xml ? generate xml and cache it and use the xml file rather going to sql server database? has anybody have any help on this? the steps invloved: 1) first generate a xml file from table something like this: select * from dbo.LOOK_UP FOR XML AUTO, XMLDATA ?SELECT * FROM dbo.LOOK_UP FOR XML RAW, ELEMENTS ?SELECT * FROM dbo.LOOK_UP FOR XML AUTO ? which one should i use and how do i access after i gnerate a xml file
Hello,I m creating forms in ASP.Net 2005 using C# language. I'm using Microsoft SQL Server 2005 and my IDs are in A001, A002, A003...and so on. How can I auto generate this IDs? Like A001 +1=A002? Please help...In SQL server 2005 which datatype i should select and how can i code in ASP.NET with C#??On button click event the data is inserted and been shown onthe grid..Thanks
The way i generate my primary keys is to keep a table
PrimaryKeyTable PrimaryKeyName : varchar (name of the primary key, for example UserID) PrimaryKeyID : int (the next key should have the number stored in that field, if it was 12 then next time i add a row the UserID will be 12)
After adding a row, i increment the value of primaryKeyID.
Is there a way to let SQL server to handle the primary key? To let SQL generate the key himself instead of me?
I am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...
Trying to generate a daily KPI report from our SQL server. I do not have access to write any functions. In the "start date" and "end date" section what numerical value would I enter to give me the equivalent of CURRENT_DATE ()?
I need to generate a text file from SQL Server. The task automatically runs daily.
The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "". The number of char includes the data parts and excludes a few word of "the number of char".
For example, The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.
I use VS2005 & sql2005express to create a website, but it cannot to display data. When I run it, I got such errors: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. My web.config file: <add name="LocalSqlServer" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf"/> When I change the User Instance=True to False, it works, but MSDN says it is not safe. I searched the past threads, which writes "delete the folder C:Documents and SettingsyyLocal SettingsApplication DataMicrosoftSqlserver Data, it will work". I try, but failed. What should I do??
I have seen many posts on the subject, but I haven't seen a clear answer to the following: 1. Start a new Web Project (VS 2005 Pro). The solution is built. 2. Select App_Data, right click and select Add New Item. 3. In the dialog box, select SQL Database and give it a name. 4. When you select Add, the message "Failed to Generate User Instance of SQL Server ... " appears. 5. I am running SQL Express. What is the correct fix to get this simple process to work? thanks, Flavelle
