I have a column named 'RELATED' of type text in a table. I want to update the contents of this colums...........but still keep what is currently stored in it. Basically i want to add some text onto the end of the string it currently contains.
Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,
I know this is possible in DB2 and Oracle, but what about for SQL-server 2005
1) select X number of rows from table1 2) I need colums for each row of table1 in a new table 3) As such, Select (select * from X where x.id = @ID), a,b,c from table Y where y.Id = @ID And I dont want to use IfExists. Thanks DK
Hi folks. This is my first post around here. So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:
Measure Month Value A June 10.00 A July 9.00 A Aug 11.00 B Jun 100.00 B Jul 98.00 B Aug 99.00 C Jun 0.75 C Jul 0.8 C Aug 0.91
I need to report the data like this: Measure Jun Jul August A 10 9 11 B 100 98 99 C 75% 80% 91%
This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.
But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.
I can restructure the database, if needed.
I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.
It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?
FROM Mdrfoit INNER JOIN DevTable ON Mdrfoit.MDRReportKey = DevTable.MDRReportKey INNER JOIN TextTable ON Mdrfoit.MDRReportKey = TextTable.MDRReportKey LEFT OUTER JOIN probLink ON Mdrfoit.MDRReportKey probLink.MDRReportKey
WHERE Mdrfoit.DateReceived BETWEEN '1/1/1995' AND '1/1/2007') AND AND (DevTable.BrandName LIKE '&PLV%')
My problom is that when i do this i get back 3 rows in my result set for every 1 result that i want So ill get like
So my result set is on average 3 time larger then its suppose to be, This is also causing my query time to be very very slow.
There are no Duplicate entries in the database where all of the fields are the same. I am confused on if this is a problom in my query. Any advice would be great
"When you have everything to loose and nothing to gain, All-In is not an option"
I have this problem that I do not now how to solve it:
I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).
If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.
Nr. Of Orders Nr. of Lines Average Delivery Time
Open Order 10 20 3.2
Closed Order 15 30 5
Total 25 50 8.2 (it should be, for example, 4.5)
Is there a way in which I can tell the system to not calculate the total for the third column?
If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.
The joined view is named "dbo.viewExecView" and is like:
SELECT Bank_No, data_center FROM [ALPHASQL2000].ev_db.dbo.Bank
The new view that joins to the above view is like:
SELECT bank.BankID, evBank.data_center AS DataCenterID FROM dbo.Bank AS bank INNER JOIN dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)
Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.
I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _SubCategories.SubCategoryName, _SubCategories.SubCategoryName, LinkName FROM _Links INNER JOIN _SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId INNER JOIN _SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List AS CREATE TABLE #TempTable ( LinkId int, LinkCategory varchar(50), LinkStatus varchar(50), LinkName varchar(50) ) INSERT INTO #TempTable ( LinkId, LinkCategory, LinkStatus, LinkName ) SELECT LinkId, _Links_Categories.CategoryName, _Links_StatusCategories.StatusName, LinkName FROM _Links INNER JOIN _Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId INNER JOIN _Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId SELECT LinkId, LinkCategory, LinkStatus, LinkName FROM #TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
Hello, and thanks for taking the time to read this.
NOOB question:
In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:
CREATE TABLE [dbo].[T_Catalog]( [StlyeID] [int] ) ON [PRIMARY] with data: 1 2
the SIZES table (filled in by the customer with all the size ranges they carry) is:
CREATE TABLE [dbo].[T_Sizes]( [SizeID] [int], [SizeName] [nchar](10) ) ON [PRIMARY] with data:
1,Small 2,Medium 3,Large 4,Xtra-Large
and the AVAILSIZES table would be:
CREATE TABLE [dbo].[T_AvailSizes]( [StyleID] [int], [SizeID] [int] ) ON [PRIMARY]
1,1 1,2 1,3 2,1 2,3 2,4
Basically, then, we know that:
style 1 comes in Small, Medium, and Large style 2 xomes in Small, Large, and Xtra large
WE know that, but getting SQL to tell us that is a major PIA!!
Now,
SELECT t_Catalog.StyleId, t_AvailSizes.SizeID FROM t_Catalog INNER JOIN t_AvailSizes ON t_Catalog.StyleId = t_AvailSizes.StyleId
will give me a nice list of each item number with a separate row for each size number.
My questions are:
How do I get the size NAMES?
How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?
Is this the right way of doing this?
In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.
Thanks for any help and information you can provide.
Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this: the formatting is messed up though
a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.
DNAMELOCNumber of PeopleSalary ------------------------------------------------------------------- ACCOUNTINGNEW YORK3 2916.67 RESEARCHDALLAS52175 SALESCHICAGO61566.67
I have a table users where there is a user_id and an department column. Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
The first line of code works fine but when I try to set the value of the property I get the following excpetion:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
I am trying to make a stored procedure in my website for updating an address:1 CREATE PROCEDURE dbo.UPDATE 2 ( 3 @add NVarchar(50), 4 @cit NVarchar(50), 5 @state NVarchar(50), 6 @zip NVarchar(50), 7 @CNum int 8 ) 9 10 UPDATE table_name 11 AppAdd = @add, AppCity = @cit, AppState = @state, AppZip = @zip 12 WHERE CertNum = @CNum When I try to save it it give me an error: Incorrect syntax near keyword 'UPDATE'Must declare scalar variable '@add'
Hi, I'm new to ASP.Net quite new to C# (My first attempt at a database website) and am trying to get a button to add "1" to "int" value called "Comments" a each time its pressed basically counting each time a comment is added. I also only wnat it to affect the row where "ModID" in my database is equal to the query string "ModID" I'm using on the page. I cannot find any tutorials so this is my best guess so far. This is probably a Noobie type stupid question but I'm stuck. This is the code I have so far for my Button_Click event: protected void Button_Click(object sender, EventArgs e){ SqlDataSource CommentCountDataSource = new SqlDataSource(); CommentCountDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["Main_Site_DatabaseConnectionString1"].ToString(); CommentCountDataSource.UpdateCommandType = SqlDataSourceCommandType.Text; CommentCountDataSource.SelectCommand = "SELECT (ModID, DateTimeLastComment, Comments) FROM Mods"; CommentCountDataSource.UpdateCommand = "UPDATE Mods SET (DateTimeLastComment=@DateTimeLastComment, Comments=@Comments) WHERE ModID=@ModID"; //How do i get the Where to use the query string info? CommentCountDataSource.UpdateParameters.Add("DateTimeLastComment", DateTime.Now.ToString()); CommentCountDataSource.UpdateParameters.Add("Comments", "10"); //"10" is just a value to test I'll change this to add "1" once I figure how. CommentCountDataSource.Update();} Sorry if I'm using the wrong lingo but as I say I'm new. If my code is a mile off then please can you send me in the right direction of some code that works.Thanks in advance if anyone can help me. Cheers,Alan
I am a DBA who is moving in the direction of minor database design. I have gone through the steps to get my tables normalized, and I am ready to get them set up so they will update from table to table. How do I set this up? I know SQL does not cascade like Access, so how is this done? From what I have read (Robert Vieira's book) I should use triggers? Any help would be appreciated. Dallas
strSQL = "UPDATE customers SET " strSQL &= "entryid = '" & strtheEntryid & " ' " strSQL &= "WHERE id =1 " right now in table id is my primary key field it increments by 1, I want to say get first record instead of saying id = 1, how to do that?
I have some code to hack in c#.. I have a database and when a form (C#) is loaded a copy of the table is made in a DataTable object. The form has a list box showing just the one column of info from the local DataTable. On the form it is possible to change a record, delete a record and add a record. These are all done in the local table.
The question, how do i send these changes back to the database. Currently it deletes the lot and copies all the local table values in. How do only do the affected row, UPDATE, INSERT etc
I have an ASP application that uses a stored procedure and ADO.net to update a sql server data file. The problem is I know the code is working, I don't have any errors with the ADO, no exceptions are caught. I can use the same basic code to insert a record using a different procedure. It is the update procedure that does not carry through.
So, I know I have a connection, the procedure works using the query builder directly so the procedure works, but when I run the code, I get no errors and no update to the datafile. I am not even sure how to trouble shoot this since I don't have an error to look up.
C# Code:-------------
private void UpdateIssue() { DateTime date = new DateTime(); date = Convert.ToDateTime(this.txtDate.Text);
//edit record in HelpDeskIssuesTbl here.
SqlConnection con = new SqlConnection("Data Source..."); SqlCommand comUpdateTicket = new SqlCommand("sp_UpdateHelpDeskIssues", con); comUpdateTicket.CommandType = CommandType.StoredProcedure; comUpdateTicket.Parameters.Add("@IssueID", this.GridView1.SelectedIndex.ToString()); comUpdateTicket.Parameters.Add("@EmpID", this.ddlEmployee.SelectedValue.ToString()); comUpdateTicket.Parameters.Add("@Date", date.ToShortDateString()); comUpdateTicket.Parameters.Add("@StatusID", this.ddlStatus.SelectedValue.ToString());
( @IssueID int, @EmpID int, @Date datetime, @StatusID int )
AS UPDATE HelpDeskIssuesTbl SET EmployeeID = @EmpID, IssueDate = @Date, IssueStatusID = @StatusID WHERE (IssueID = @IssueID) RETURN
Like I said the Stored Procedure does work when I run it directly in Visual Studio. I have double checked all the params and they all match up unless I am missing something.
My feeling with the SCD component is not that very solid. I have the feeling that the behavior of the insert/update strategy is not always correct an working. I will describe two problems that i encounter.
1. My destination table contains records with the value ''. Cause i don't want '' ( 2 single quotes) in our DWH i update the view that is the source with a case statement that changes the '' to NULL. But when i run the packages the '' values are not update with the NULL values. When i delete the destination table and run the package, the records are inserted with the NULL value as expected. Anyone who has experienced this problem?
2. When i create a new table and run the package so the destination table gets filled with records the SCD will insert alle records (for example 100). When i start the run directly after the first run, all records are updated instead of doing nothing what it should do cause all records exists.
I am looking for a way to update a sqldatasource what I have is a ASP Wizard applicationstep oneis a dataview with the select ability it displays an ID and Namein step two what i want it to do is take the ID from step ones select and put that into the where clause so I have select * from table where id = step1selectedID