tsql

Address Match Key Algorithm

I have a list of addresses in two separate tables that are slightly off that I need to be able to match. For example, the same address can be entered in multiple ways: 110 Test St 110 Test St. 110 Test Street Although simple, you can imagine the situation in more complex scenerios. I am trying to develop a simple algorithm that will ...

Getting Last Id from a Cross Server Insert using SQL Server

I am running a cross server insert INSERT INTO server.database.dbo.table (Field) VALUES('test') Afterward, I need to get the id of the last insert. However, when I run the the scope_identity function, I don't get the latest id from the foreign server. SELECT @ID=SCOPE_IDENTITY() How would I retrieve the last id from a cross server ...

How can I optimize this SQL query?

Right now I have this SQL query which is valid but always times out: SELECT ( SELECT (MAX(WP_ODOMETER) - MIN(WP_ODOMETER)) / DATEDIFF(DAY, MIN(WP_DATETIME), MAX(WP_DATETIME)) FROM WAYPOINTS WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GETDATE() AND WP_VTDID = 'L088' ) AS MAXD, ( SELECT MAX(WP_ODOMETER) FROM WAYPOINTS WHER...

How to insert a range of keys in another column

I've just gone blank. I have a many to many relationship with three tables. Lets call them A, B and C. C stores the primary keys of the other two tables. Now, I would like to insert all primary keys from table A, and lets say primary key 1 from B into table C. I just don't get it. All I came up with was some stored procedure that runs ...

T-SQL - What is an inline-view?

I recently answered this question how-to-call-user-defined-function-in-order-to-use-with-select-group-by-order-by My answer was to use an inline view to perform the function and then group on that. In comments the asker has not understood my response and has asked for some sites / references to help explain it. I've done a quick googl...

Return the id of the just added row

In a similar vein to my previous question I again ask the SO guys for your collective wisdom and help. In a stored procedure and after passing some checks I need to insert a new row and return the newly created id for it. The check if a row exists works so it is the bit after that which I am undecided upon. The table has two important ...

TSQL Parameterized SPROC question

I need to write a stored procedure to update one of a set of similar columns. The columns are named 'UserField1', 'UserField2' etc. I was hoping to pass a parameter to the SPROC which would set the column to be updated. However, I can't seem to get the code correct. Here's a simplified example of what I tried (which gets me an 'Incor...

Loop in stored procedure or in client code

From an efficiency and best practices point of view, I appreciate everyones thoughts. I have a stored procedure that makes (n) copies of a row and inserts the row into the necessary table. For example. Make a copy of an inventory item and add the copies to inventory. I see two options for this type of stored procedure. Option 1: CR...

What are "Instead of" Triggers?

In SQL Server, what are Instead Of triggers? ...

How can I parse dynamic SQL before execution in T-SQL?

I'd need to know how to parse a query to know if it is well build before executing it. If the parsing is correct then execute it, if not discard it. I haven't been able to accomplish this using SET NOEXEC, SET PARSEONLY, TRY/CATCH. I m using dynamic sql inside a loop. Sometimes the dynamic sql is incorrect not for my fault but for the ...

ADO.NET calling T-SQL Stored Procedure causes a SqlTimeoutException

I have a T-SQL stored procedure with the signature CREATE PROCEDURE MyProc @recordCount INT OUTPUT @param1 INT ... When executed directly in Sql Server the procedure runs in under 5 seconds, returning a few result sets amounting to about 100 rows in total. Calling this procedure using the ADO.NET SqlDataAdapter.Fill method to populat...

Finding out which locks that are acquired in a query on SQL Server?

I have a SQL statement from my application. I'd like to know which locks that statement acquires; how can I do that with SQL server? The statement has been involved in a deadlock, which I'm trying to analyze; I cannot reproduce the deadlock. I'm running on MS SQL Server 2005. ...

WHILE loop in SQL

I have two tables: Holdings and Transactions Holdings data looks like: 06/30/2009, A, 100 06/30/2009, B, 1200 06/30/2009, C, 100 06/30/2009, D, 100 Transactions data looks like: A, 06/05/2009, 100 B, 06/02/2009, 400 B, 06/13/2009, 400 B, 06/28/2009, 400 C, 06/17/2009, 100 D, 06/30/2009, 100 Ok, so what I need to accomplish is t...

TSQL Insert Conundrum

I have a table. The table needs to store a number values about a location, so initially I had just the two columns without the incrementing column, giving the following: RefID | TypeID 1 | 1 1 | 3 1 | 6 2 | 3 3 | 5 3 | 6 Where the first column is the reference for a location and the second is the actual values....

SQL Dynamic DatePart when using DateDiff

Is there a way to pass the DatePart parameter of DateDiff as a variable? So that I can write code that is similar to this? DECLARE @datePart VARCHAR(2) DECLARE @dateParameter INT SELECT @datePart = 'dd' SELECT @dateParameter = 28 SELECT * FROM MyTable WHERE DATEDIFF(@datePart, MyTable.MyDate, GETDATE()) < @dateParameter The only ...

CREATE TABLE statement question in T-SQL

I am working on a pomotion database and below is what my CREATE TABLE steatment looks like: CREATE TABLE [dbo].[sponsors] ( [InstId] [bigint] NOT NULL, [EncryptedData] [varbinary](44) NOT NULL, [HashedData] [varbinary](22) NOT NULL, [JobId] [bigint] NOT NULL, CONSTRAINT [PK_sponsors] PRIMARY KEY CLUSTERED ( ...

Can a trigger be forced to run in a separate context than the user in SQL Server 2000?

Let's say I have a SQL Server 2000 table, any name will do, it's irrelevant for this topic. On this table I have a trigger that runs after update or insert. The user is able to insert and update the table on which the trigger is attached, but not on other tables that the trigger targets. If the user modifies data in the original table...

Is it possible INSERT SELECT a collection of aggregate values, then Update another table based on the @@IDENTITY values of the inserts you just made?

I'll begin by admitting that my problem is most likely the result of bad design since I can't find anything about this elsewhere. That said, let's get dirty. I have an Activities table and an ActivitySegments table. The activities table looks something like: activityid (ident) | actdate (datetime) | actduration (datetime) | ticket...

Converting PascalCase string to "Friendly Name" in TSQL

I have a table with a column whose values come from an Enumeration. I need to create a TSQL function to convert these values to "Friendly Names" upon retrieval. Examples: 'DateOfBirth' --> 'Date Of Birth' 'PrincipalStreetAddress' --> 'Principal Street Address' I need a straight TSQL UDF solution. I don't have the option of install...

Is there a way to execute T-SQL code inside a Dataflow in SSIS?

Background I have a dimension table that has a single record for each day. Each record has a primary key so example data would be: Dimension Table --------------- --------------------------------- | ID | DateTime | --------------------------------- | 1083 | 04/10/2008 10:02:00 PM | --------------------------------- ...