The problem: I have a table that records data rows in foo. Each time the row is updated, a new row is inserted along with a revision number. The table looks like: id rev field 1 1 test1 2 1 …
Tag: tsql
SQL – Subtracting a depleting value from rows
I have a situation where I need to take a “quantity consumed” from one table, and apply it against a second table that has 1 or more rows that are “pooled lots” of quantities. I’m not sure how to describe it better, here’s what I mean from a table perspective: I need a resu…
insert into values with where clause
I am trying to programmatically enter values into my table. I cannot use a straight Select @variables. I have to use the keyword Values. How can I create a where clause when using Values in the insert into. I am trying to avoid duplicates Answer
Simple update query is taking too long
I have a table CurrentStatus in my database (subscription database in a merge replication) Columns are StatusID {Primary Key + Clustered Index}, StatusName, StatusDate, UserID,CreatedDate, …
How to select info from row above?
I want to add a column to my table that is like the following: This is just an example of how the table is structured, the real table is more than 10.000 rows. So for every time there is a value in ‘Subgroup’ I want the (New_Column) to get the value [No_] from the row above There are cases where
SQL Server – Cursor
I am trying to loop through a table using a cursor: That above returns taskResourceOID and EvUserOID. If I need to output a table with the @TaskOID and the respective taskResourceOID and EvUserOID, what is the best way to do it? Answer Use a temporary table or a table variable.. Or even better, don’t us…
Creating a UDF(User Define Function) if is does not exist and skipping it if it exists
Hi and thanks for reading this. I am trying to use the IF EXISTS/IF NOT EXISTS statement to check if an Object exist. Basically I want to skip it if it is there or create it if it is not there. I …
Changing the maximum length of a varchar column?
I’m trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I’ve dropped and re-created tables before but I’ve never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: ALTER TAB…
Showing what quarter of a financial year a date is in
I’m trying to construct a query that will map two columns, one, a date from a table, the second column an alias to show what quarter and financial year the date falls into. Unfortunately I don’t have enough knowledge of SQL to know where to begin. I know that I’d do this with a combination o…
What is better to return single value from stored procedure to .Net: OUTPUT parameter or ExecuteScalar?
I need to create a stored procedure that needs to return a count of some records. I’m using .Net to read the result. I can use an OUTPUT parameter to return the value or I could do a select count(*) …