tsql

Variant use of the GROUP BY clause in TSQL

Imagine the following schema and sample data (SQL Server 2008): OriginatingObject ---------------------------------------------- ID 1 2 3 ValueSet ---------------------------------------------- ID OriginatingObjectID DateStamp 1 1 2009-05-21 10:41:43 2 1 2009-05-22 12:11:51 3 1 ...

SQL get single value inside existing query?

Hi :) I have a query that returns a bunch of rows. But using the same query i would like to: 1. get the total row count in the table 2. get the row number where a certian username is located Right now im doing like so: BEGIN DECLARE @startRowIndex INT; DECLARE @PageIndex INT; DECLARE @RowsPerPage INT; SET @PageIndex = 0; SET ...

T-SQL Syntax, What are all usages of the @ sign?

What are all of the different usages of the @ sign in T-SQL? btw, this was an interview question. @var (variable) @@identity (system variable) What are others? ...

Selecting multiple tables from single table by distinct column value in stored procedure

I have a bit of a conundrum I need to deal with, and I'm trying to find a good solution to it. I'm using MS SQL server 2005, and given a table where a uniqueidentifier column contains a data key to other information on the server. What I need to do is select every row in this datatable (sproc from ASP.NET), but separated into tables ba...

regEx to transform tsql Insert on Update command

I'm not very skilled with regex, I was wondering if it was possible to use a regular expression to transform a string like insert into tblTest (id,title,col1,col2) values (1,'test','test1','test2') into update tblTest set title='test',col1='test1',col2='test2' where id=1 btw, the insert query will not be always like the one I've wr...

Use APPLY with an UPDATE statement as the row source

I tried to perform the following in order to update a psuedo-identity value at the same time as using the value to create new rows, but APPLY does not like UPDATE statements as the right table source. What's the most elegant alternative outside of simply using an identity column? create table Temp1( id int not null identity(1,1) pr...

SQL Custom Replication Via Triggers

As I have seen, SQL 2008 no longer offers replication over internet in very simple method. Not only that, I can see that the merge replication method installs too many triggers and views onto database and adds additional field named "rowguid". We have more disc space available today then the processor speed. So I decided to write my own...

retrieve most recently executed SQL command (T-SQL)

One of my developers working on a trigger-based logging facility in SQL Server 2008 asked me if there was a command to retrieve the most recently executed SQL command within T-SQL. I thought there was a system stored procedure for just such a function, but it's possible I'm thinking of another product from a prior decade... online searc...

Does SQL Server Offer Anything Like MySQL's ON DUPLICATE KEY UPDATE

In MySQL, if you specify ON DUPLICATE KEY UPDATE and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect: INSERT INTO table (a,b,c) VALUES ...

Updating a Table from a Stored Procedure

I am trying to learn database on my own; all of your comments are appreciated. I have the following table. CREATE TABLE AccountTable ( AccountId INT IDENTITY(100,1) PRIMARY KEY, FirstName NVARCHAR(50) NULL, LastName NVARCHAR(50) NULL, Street NVARCHAR(50) NULL, StateId INT REFERENCES STATETABLE(StateId) NOT NULL ) ...

TSQL Constraints on Temp Tables

Very quick and simple question. I am running a script to import data and have declared a temp table and applied check constraints to that table. Obviously if the script is run more than once I check whether the temp table already exists and if so, I drop and recreate the temp table. would that also drop and recreate the check constraints...

TSQL question : how to iterate columns of result set

Hi, I have a select statement and a cursor to iterate the rows I get. the problem is that I have many columns (more than 500), and so "fetch .. into @variable" is impossible for me. how can I iterate the columns (one by one, I need to process the data)? Thanks in advance, n.b ...

SQL JOIN vs IN performance?

I have a case where using a JOIN or an IN will give me the correct results... Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using MSSQL) Thanks ...

SQL: Populate single row(tbl 1) from multiple rows(tbl 2); cross DB

I have created a single table in my primary DB called tblGlobalIDMapping that will assign a GUID to all entries and store the other 3 primary ID's across the App to provide for a single ID repository. The new Table is in the following structure --> AppGlobalID uniqueidentifier NoNulls PersonID int NoNul...

Filter the result set of a stored proc using a where clause

I'm looking to filter the resultset of a stored procedure. What I'd like is something like the following (non-working) syntax: IF EXISTS ( SELECT 1 FROM (EXEC sp_linkedservers) WHERE srv_name = 'myServer' ) PRINT N'dropping linked servers' GO edit - this is just one example, I'd like a general solution if possible ...

How can I get the number of records affected by a stored procedure?

For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1. How do we get the number of records affected by a stored procedure? ...

Labor Day Vs. Thanksgiving

I am creating a calendar table for my warehouse. I will use this as a foreign key for all the date fields. The code shown below creates the table and populates it. I was able to figure out how to find Memorial Day (last Monday of May) and Labor Day (first Monday of September). SET NOCOUNT ON DROP Table dbo.Calendar GO Create Table d...

Is MonthName a reserved word in SQL 2008

I am creating a table that has a column called MonthName. When I defined this column the word "MonthName" showed up in blue like it was a reserved word. So I tried to look it up. I cannot find it in Books Online or on Microsoft's site or even on our own SO. I tried sp_help "monthname" and the reply was "The Object 'monthname' does not ...

Is FULL OUTER JOIN really such a bad thing here?

In general, I believe "FULL OUTER JOIN Considered Harmful", to turn the phrase. Background: http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx But I do have a specific situation where it would be really handy: Given: CREATE VIEW Calcs(c1, c2, c3, fordate, ...other columns) AS /* Complicated set of equations...

How to convert SQL Server XML type value (xsi:nil) of DateTime to null

Is there a way to query SQL Server XML type so that for an element with xsi:nil="true", return null instead of default datetime value, which is 1900-01-01 00:00:00.000? here is a code snippet declare @data xml set @data = '<DOD xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />' select Value1 = @data.value('/...