Check if a row exists, otherwise insert

I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction. This is for a booking system, so it must be...

How to count instances of character in SQL Column

I have an sql column that is a string of 100 'Y' or 'N' characters. For example: YYNYNYYNNNYYNY... What is the easiest way to get the count of all 'Y' symbols in each row.

Best equivalent for IsInteger in SQL Server

What is the best way to determine whether or not a field's value is an integer in SQL Server (2000/2005/2008)? IsNumeric returns true for a variety of formats that would not likely convert to an...

Bizarre WHERE col = NULL behavior

This is a problem one of our developers brought to me. He stumbled across an old stored procedure which used 'WHERE col = NULL' several times. When the stored procedure is executed it returns...

What is the significance of 1/1/1753 in SQL Server?

Why 1753? What do they have against 1752? My great great great great great great great grandfather would be very offended.

Concurrency - one process updates, another inserts

I have two processes that work with data in the same table. One process inserts daily, one by one (pure ADO.NET), about 20000 records in the target table. The second process calls ( periodically,...

Is it possible to have a function without a parameter in t-sql?

I am being forced to put a @noparameter varchar(1)=null in this function? How do I create a function to return a table which has no parameters! ALTER FUNCTION...

Simple DateTime sql query

How do I query DateTime database field within a certain range? I am using SQL SERVER 2005 Error code below SELECT * FROM TABLENAME WHERE DateTime >= 12/04/2011 12:00:00 AM AND DateTime <=...

Generate Dates between date ranges

I need to populate a table that will store the date ranges between 2 given dates: 09/01/11 - 10/10/11 So in this case the table would start from 09/01/11 and store each day till it got to...

Update XML node (in an XML column) in SQL Server 2005 with another column value in the same row

I have a temp table with an XML column (XMLColumn) and a VARCHAR column (ABCColumn). I am trying to replace the value of a particular XML node within XMLColumn with the value that is in ABCColumn...

Inner join returning more rows then exist in tables

Till today my thoughts about the inner join were it will return the minimum number of rows that exist in tables satisfying a joining condition. Ex. if table A contains 4 rows and table B contains...

Execute SQL Asynchronously or change locking from Trigger

I have a complex unit of work from an application that might commit changes to 10-15 tables as a single transaction. The unit of work executes under snapshot isolation. Some of the tables have a...

How can I create a table using distinct values from a column as the column names for the new table?

I have a table with a Tag column with about 1000 distinct tags. I'd like to create a new table with a column for each distinct tag, the column name would be the tag value. Is there a nice and easy...

Trigger for updating total records on both insert and delete

I'm writing a trigger to store the record count of one table as a column in another to speed up some reporting queries on a large db. Here's what I've got so far, it works fine on deletes but I...

SQL Server 2008 Complex Query & Table Join

This is a little difficult to explain but I will try my best. I have a database which maintains information on Marine Shipping etc. I have the following columns to work with. (There are others but...

How to update Identity Column in SQL Server?

I have SQL Server database and I want to change the identity column because it started with a big number 10010 and it's related with another table, now I have 200 records and I want to fix this...

SQL count of distinct records against multiple criteria

I have a table in SQL SERVER "usage" that consists of 4 columns userID, appid, licenseid, dateUsed Now I need to query the table so that I can find how many users used an app under a license...

Get number of employees who worked in more than one department with SQL query

I'm trying to figure out a query which show the number (amount) of employees who worked in more than 1 departments. Here the table name and fields: Employee(id_employee, employee_name,...

Printing integer variable and string on same line in SQL

Ok so I have searched for an answer to this on Technet, to no avail. I just want to print an integer variable concatenated with two String variables. This is my code, that doesn't run: print...

best number of rows to delete in a delete query

I am using SQL Server and I have a very big table. If we use delete from table query we have jump in transaction log. I should use a query like this delete top (@count) from table And then I...

Running SUM in T-SQL

Sorry for bad topic but I wasn't sure what to call it.. I have a table looking like this: +-----++-----+ | Id ||Count| +-----++-----+ | 1 || 1 | +-----++-----+ | 2 || 5 | +-----++-----+ |...

T-SQL create table statement will not accept variable

Why can I not use a variable to name a new table? As a beginning SQL project, I'm making a personal finance database. Each account will have a corresponding table in the database. There is also a...

SQL Server - How To Properly Update Table With User Defined Function In Update Statement?

Have an issue attempting to update a table using a user defined function in the update statement. I have greatly simplified my existing SQL, and provided some sample code to show the issue that I...

How to split one column value into 2 separate columns in SQL Server?

Select physical_name From Sys.database_files How to split the out put file path into 2 parts? For example: Select physical_name From Sys.database_files produces this output: C:\Program...

Most efficient way to execute multiple scalar functions per row

I am trying to speed up my query. I need to run 2 scalar-valued functions per row with a couple of the columns as parameters. The functions do a calculation based on running totals, and there is...

Dynamic SQL output of a query to a variable

I would like to output the result of the dynamic SQL into a variable called @Count but not sure what the syntax or even the code should like to accomplish this. The code looks as follows: declare...

row number without using the ROW_NUMBER window function

Suppose you have a table with non-unique values such as this: CREATE TABLE accounts ( fname VARCHAR(20), lname VARCHAR(20)) GO INSERT accounts VALUES ('Fred', 'Flintstone') INSERT accounts...

How to have count text like in SQL?

How to have count text like in SQL ? IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t Create table #t (message nvarchar(4000), messagedate datetime) insert into #t values ('Column...

How do I SELECT minimum set of rows to cover all possible values of each columns in SQL?

I am running a SQL query to get data from a table to map all different possible values of all categories represented by each columns. How do I run the SELECT query such that it returns the minimum...

Azure SQL trial balance previous month

I have a table with data. The opening balances are pre calculated for each year, there is always a figure for January. I want the opening balances to carry over for each month, including any net...