I am accessing rows in an SQL database table based upon the contents of the Primary key and another column. But, I cannot insert new rows in the table when the content of the new Primary key column already exists in another row. Can I define the table with a multiple column Primary key so that a unique value can be based upon the content of both columns?
View 3 RepliesI am accessing rows in an SQL database table based upon the contents of 2 Primary key columns. But, I cannot insert new rows in the table when the content in one of the new Primary key columns already exists in another row. I get an error message that says: "Violation of PRIMARY KEY constraint 'PK_ReferendumVoters'. Cannot insert duplicate key in object 'dbo.ReferendumVoters'. The statement has been terminated."
The reason why this error is occurring is because I first stored "Referendum 1" in one column along with "me" in the second column of a new row for a "ReferendumVoters" table. Then, I attempted to insert a new row with the same "Referendum 1" string in the first column along with a different name in the second column. But, it will not let me do this even though I created a new index that defines the two columns as unique. Is there any way that will enable me to do this?
I am having trouble deleting three table rows using SQL. The middle table is a junctional table allowing a one to many relationship between table 1 and table 3.
Table 1
Order_Id int Primkey FK1
Name Varchar
Date Datetime
Table 2
Order_Id int PrmKey FK1
Item_Id int PrimKey FK2
Table 3
Item_Id int PrimKey FK2
Description Varchar
Price money
Acually, I want to delete all rows associated with any particular order.
I have four tables namely tblStudent, tblParent, tblEmployee and tblUnwantedVisitor.
tblUnwantedVisitor should get the primary key from the three tables, which is the visitor ID, tblStudent visitor ID starts from 10000, tblEmployee from 20000 and tblEmployee from 30000.
I used foreign key from one to one table only, so I don't know if my approach for the database is wrong or foreign key for multiple tables, which is somehow unlikely, is necessary.
I have two tables, Customer and CustomerPhone.
Single record in Customer can have multiple CustomerPhone records. As you see in the image below, Phone and Fax resides in CustomerPhone table whereas the rest of the fields resides in Customer table. If user want to edits a customer record, obviously i will have to update the record in Customer table and at least two records from CustomerPhone (Phone and Fax).
I could write two update statements, one Update customerPhone and the second update Customer table. Is there a better solution?
SELECT u.Email FROM UserProfiles p, aspnet_Membership u WHERE p.RulesCheckBox = 'True' AND u.UserId = p.UserId This gives me a list of registered member email addresses that have selected the RulesCheckBox in their profile, so I can email them with an email script. There is a relationship between the Membership table and the UserProfile table, so it finds the email addresses from the Membership table where that user has the RulesCheckBox checked in their profile (in UserProfiles table). I'm wanting to add some additional names to this list, from a different table. The purpose is so I can manually add email addresses to the additional table, so the additional email addresses will also receive the same email, even though they aren't registered members of the site. So how can I adjust that SELECT statement so this one is combined with it somehow?:
SELECT Email FROM ExtraEmails WHERE Rules = 'true'
I have excel file with columns EmpName,Date1,Date2,...Date7 .I want to insert EmpName into Employee table after inserting i will get EmpNo(Identity column) with that i need to insert Date1,Date2...Date7 into TimeSheet table.
My Excel structure is like this
EmpName 8/1/2010 8/2/2010 8/3/2010 8/4/2010 8/5/2010 8/6/2010 8/7/2010
Naresh 17:00-2:00 17:00-2:00 14:00-12:00 7:00-12:00 7:00-12:00 .. Off
Similarly 500 employees data
I want to insert name i.e into Emp table after inserting i will get EMpNo i.e identity column with that i should insert Date1(8/1/2010) i.e excel header to date7 into Timesheet table Date column and corresponding time into TimeIn and TimeOut.I will split the time.I just want write Stored procedure for this task.
My database tables are like this
Emp-EmpNo,EmpName,CompaignId
Timesheet-TimeID,EMpNo,Date,TimeIn,TimeOut,TotalHours
Date in Timesheet should be like this
TimeId EmpNo Date TimeIn TimeOut T otalHours
1 100 8/1/2010 17:00 2:00 9hrs
2 100 8/2/2010 17:00 2:00 9 hrs
7
I just want this logic on how to insert Date1 to date 7 as 7 rows into Timesheet table and EMpName into EMployee table.while accesing each row i need to insert Date 1 to date7 as 7 rows into Timesheet table.
We have a database with a lot of information about Persons. I won't post the entire database structure because it is too big, but it looks something like this:
Person
ID
Name
Street
City
State
Country
Language
LangCode
Language
Interest
ID
LastChangedBy
LastChangedOn
LocalizedInterest
InterestID
LangCode
Description
PersonInterest
PersonID
InterestID
Now, this is just a small example. In our database, we have about 8-9 localized tables (like Interest) a Person can be linked to. A Person can have multiple Interests, a Person can have multiple Jobs, a Person can have multiple Educations, a Person can have multiple Experiences, I have to build a search function. Let's say you enter "tom" as the search term. This should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables.
If you enter multiple words to search for (eg. "tom php"), it should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables AND "php" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables. At the moment there are about 4,500 records in the Person table and if I do an outer join of Person with all the tables I have to search, there are about 1,300,000 records and 40-50 fields to search in.
How should I approach this problem so that performance will be acceptable? The client expects "something like Google" in terms of speed and ease of use. We're using MS SQL Server 2000 and ASP.NET 2.0. The search functionality has to be added to an existing application and changing the technology or database structure is not an option.
Was wondering if someone can help me out with this multi update problem
I have a webform that populates a single table but due to certain dropdown data conditions it could also potentially populate 3 other tables
The tables information populates a gridview with amounts from accounts etc..
e.g.
Table 1
Site DataType account update
34 SS 12.50 N
Table 2
Site account
34 12.50
Say the above entry has the updated column changed to Y via the webform the amount 12.50 should disappear from the gridview. I was
a bit shortsighted and added the additional tables afterward. The information on the gridview pulls from the additional tables and since
the first table and the additional tables are not linked the data still stays the same in the gridview
I do have an Id column on both tables but unfortunately I added the rest of the tables after the first one so the Id's do not really match.
I have 3 tables
Restaurants
IDNameAddress
Location
ID (Foreign Key)LocationID
Food
ID (Foreign Key)FoodID
Here's what I want to do, I have two variables @FID(food ID) and @LID(LocationID). I want something like
select * from restaurant where ID in (( select ID from location where locationID = @LID) AND (select ID from Food where FoodID = @FID))
The bold part is where I'm having problems. I want it to check both the Location and Food table for a match.
I'm having a hard time putting it into words :p I hope you get what I mean
Actually I have a gridview with many rows and I want to insert the all rows in sql server through one store procedure first row can inserted but what should we do for other rows
View 3 RepliesHow can I combine multiple rows into a comma-delimited list in sql server in build function?? in HCL
examples :
X
---
12
15
18
20
Output : 12, 15, 18, 20
with in build function of sql server.
Just looking through database design and wondering what would be the better option. I have customers, who all have orders, pickings, deliveries and invoices. Now an invoice can't exist without a delivery, a delivery can't exist without a picking and a picking can't exist without an order. So I could set the tables up in a linear fashion. e.g.
Customer -> Orders -> Picking -> Delivery -> Invoice
then in Orders, there would be an ID field, with a customerID field and in Picking, there would be an ID field, with an orderID field and in Delivery, there would be an ID field, with pickingID and in Invoice, there would be an deliveryID field. Linking them altogether, again, in a 'linear' fashion.
The problem is if I want to get, say, all the invoices for a specific customer, my query would be something like
[Code]....
My other option would be to have in a customerID column in each of the tables meaning a much easier query
[Code]....
Originally "LoadDate" column (see below scripts) was there both in tables DIM_Table_1 and DIM_Table_2
According to change in requirement, LoadDate in both the tables to be chnged to LOAD_DATE. It has to be done through script
EXEC sp_rename 'dbo.DIM_Table_1.LoadDate',
'LOAD_DATE', 'COLUMN' -- Successfully executed
EXEC sp_rename 'dbo.DIM_Table_2.LoadDate',
'LOAD_DATE', 'COLUMN' -- Error: See below
Error: The new name 'LOAD_DATE' is already in use as a COLUMN name and would cause a duplicate that is not permitted.
I'm a novice in .NET programming. I was trying to figure a way to make a MULTI TABLE search via a stored procedure for my website. I've written a SP to select records from one table, which isn't working for some unknown reasons:
[Code]....
Whatever I search for, the datagrid is displaying all records from my table.
Is there a way to select data from multiple tables other than the UNION ALL statement. Addtionally I need to select data from a different database.
My current statement is as follows (there are alot more fields but I have shortened it ):
SELECT server.dbo.SRFILE.SR_GROUP, server.dbo.SRFILE.SROWNERSHIP, server.dbo.SRFILE.SRCONMAE, BSFLBWF_1.Incidentx,
BSFLBWF_1.Severityx FROM server.dbo.SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = server.dbo.SRFILE.SRONUMBER
WHERE (BSFLBWF_1.Closex IS NULL).........
I have a website where users are going to enter recurring meetings/events. What's the most efficient way to insert this data? For instance, let's say a user wants to store a meeting for every monday at 8am. This would be a weekly meeting and I would run a loop and add 7 days to each meeting and then insert each row into SQL. Is it efficient to just run the insert command (cmdinsert.executenonquery()) for every week?
View 4 RepliesOne of my peer wrote a query based on multiple tables using joins. One of the table is in a different database The query was taking much time
1. Create an SP
2. Create a temp table and copy all records from the external db
3. Avoid using Left Join as much as possible and use Inner Join
4. Create and use indexes
5. Remove tables/columns that are not necessary
In this scenario, I would like to suggest to use Covering Index, but how can it be created for multiple tables and a temp table?
I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different database that contains information regarding the group of the data.
I so far have the following:
SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx
I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.
I have an C# ASP application I am writing that needs to have the capability to import a generated excel or a comma delineated sheet each day. A clerk will have this job each morning so it doesn't need to be automated. My problem in trying to understand the solution to this is that the 1 sheet contains loan information, including customer information all in the same sheet. I would like to send certain columns to update information in the loan table and send other information to update the customer table. I need it to create relationships when new loans appear in the spreadsheet.
View 1 Repliesi have a select statement which returns a column value.
id like to display the lowest column value there is, but if there are multiple rows with the same (and lowest) value id like it to be random which one of the rows is selected.. how would i do this? my select statement is currently:
SELECT TOP (1) url, ABS(r1 - @r1) + ABS(r2 - @r2) AS difference
FROM type
ORDER BY difference
this picks the one lowest result for me, but if there are multiple duplicate lowest results id like to select them, and show one at random..?
I have several rows in my database like this
ID Status Type Count Event
291 0 0 2 1
523 1 2 0 4
and so on. When my user performs some basic actions on my website, some values are passed through JSON to the server and are then converted into custom objects with 5 int properties(so they match the table). These objects that the server receives are the rows to be updated(based on ID) and the values for each column. How can I do this in one SQL statement without the need to loop through each object and update them all seperately? Is there a way? Preferably compatible with SQLServer 2005.
I've a table where one person teaches multiple classes. It looks like this.
Table1
Id Name Class Subject
----------------------------------------
2 Sam 12 English
2 Sam 10 Maths
2 Sam 10 English
2 Sam 8 History
2 Sam 12 Economics
Here Sam teaches Class 12 - English & Economics, Class 10 - Maths & English, Class 8 - History
I want to display the result like this -
Id Name Class Subject
---------------------------------------------------------------
2 Sam 12 English, Economics
2 Sam 10 Maths, English
2 Sam 8 History
How to concatenate multiple Rows?
I am looking for a better way to update a list of records. I have a string column in my table that contains an array of strings like so:"11112,AAAA1,AAAA2,99998" etc. It is possible that the column could contain one of those values at any position, for instance, these are ALL valid values for the column:
"11112"
"AAAA1,11112"
"11112,AAAA1"
"AAAA1,11112,AAAA2"
I want to write a SQL statement that will remove "11112" from all of the columns when needed. This MUST also remove any leading, trailing, or double comma values that may be left from simply replacing the "11112" with an empty string.As of now, I simply load a SqlDataReader with any records that contain "11112", I replace it with nothing, "", then I fix the string if need be and update it.
I have extended a gridview to add an additional header with following two hyperlink controls "Select All" and "Clear All". These will operate on checkboxes in the data rows of the grid. I hide the column headers as I only want to show one column with checkboxes and "Select/Clear All" links. Everything is working as expected. Now, I wanted to add a scrollbar to my grid control, I did add the scroll bar using div but what it does is, it includes the "Select All and Clear All" links aswell. I only need to add the scrollbar to datarows and not to the header.I am trying to extend my control to include hte scrollbar by adding div during Render function. But how will I determine or loop through header rows? How will i get the header row with hyperlinks that I created above?? In Render if I do this.HeaderRow, it gets the original column header and not the custom header.
View 1 Replies