I have a sql table which has one row of value. In my application i want to rename the column name as the first row values. I am using as keyword but waht do i put after 'as' for the first row values
I read some articles before posting to try and solve my issue but i didn't get anywhere,
Here is what i'm trying to do:
1) I have an excel sheet with 1 EntrantID column and the rest are numbers (these numbers refer to answer codes for certain questions),
So for example, my excel sheet looks something like:
ID --- a1 --- a2 --- a3 --- a4 --- a5 --- etc...
1 ---- 32 --- 55 --- 12 --- 121 -- 50---etc...
The goal is to traverse thru every row, and every column and according to a checking condition for the values of answers, i will add the number to one of two columns in another table, so for example, i will start traversing a1, i will check it's value which is 32, if 32 > 0 and 32 < 60, then i will add 32 to columnA in another table, else, i will add it to column B in that table, then i will move on to the next value, a2 which is 55, it's < 60 so i will 'add' it to that same columnA in that table..
At the end, my other table, the one with results should look like this:
PersonID --- columnA --- columnB
1 ------------ 32, 55, 12 --- 121, 50
Hope i didn't confuse things, i'm trying to explain as best as i can lol.
So what i did, i imported the excel sheet to sql server, and i looked up SQL language to see if i can do that thru SQL itself, and i couldn't find a way to traverse columns, so next, i started an ASP.NET project, connected to the DB and i drag/dropped the table in my page as a GridView, where i'm trying to traverse it so i can get the values, check them against the range, and insert them in relative column.
I tried something like this:
DataTable dt = new DataTable(); dt = (DataTable)GridView1.DataSource; DataTable temp = new DataTable("MyTable"); for (int i=0; i < dt.Rows.Count-1; i++) for (int k = 0; k < dt.Columns.Count-1; k++) if ((int)dt.Rows[i][k] > 0 && (int)dt.Rows[i][k] < 50) temp.Rows[i][k] = dt.Rows[i][k];
That is not working, and i'm unable to do it. basically, i don't know how to fetch the value inside each [row][column], and overall i don't know how i can do the required task.
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?
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.
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.
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.
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
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 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?
One 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.
I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets. I found a very good article in [URL].[URL] was able to successfully export each of my datatables in the dataset to excel worksheets. This solution worked for small number of rows. But when the data got larger I am consistently getting the system.outofmemoryexception. THis is because the code is using up the memory when creating excel.
