tsql

Is there a MSSQL command to merge lots of rows data into a single row from a common key?

I have a few tables filled with info like this: jobDetails: +-----------+------+---------+ | JOBNUMBER | DATA | KEY | +-----------+------+---------+ | 6015425 | .... | COLOUR | +-----------+------+---------+ | 6015425 | .... | SIZE | +-----------+------+---------+ | 6015425 | .... | WEIGHT | +-----------+------+---------+...

FK on same table

Hi all, I have table with ID who is PK and I have in same table column ParentID who is in some cases ID's.(I say some cases because for example ParentID can be 0 but this ID not have in ID's column). I try to create Query: ID ParentID Value 1 0 Test 2 0 Test01 3 2 Test02 4 3 ...

What can cause 'rows affected' to be incorrect?

Using Microsoft SQL Server Management Studio 2008. I have done a simple transaction: BEGIN TRAN SELECT ko.ID, os.ID AS ID2 FROM table_a AS ko JOIN table_b AS os ON os.ID=ko.ID WHERE (ko.the_date IS NOT NULL AND os.the_date IS NULL); UPDATE table_b SET the_date=ko.the_date FROM table_a AS ko JOIN table_b AS os ON os.ID=ko.ID WHERE (ko....

Need approach for working with small subsets of a large dataset

I am facing a conceptual problem that I am having a hard time overcoming. I am hoping the SO folks can help me overcome it with a nudge in the right direction. I am in the process of doing some ETL work with the source data being very similar and very large. I am loading it into a table that is intended for replication and I only want...

sql update query syntax with inner join

Can anyone find my error in this query? I'm using sql server 2000 and I want to update all entries in the CostEntry table to the corresponding value in the ActiveCostDetails table. The where clause DOES work with a select statement. UPDATE CostEntry CE INNER JOIN ActiveCostDetails As AD ON CostEntry.lUniqueID = ActiveCostDetails....

problem with WHERE clause matching on Arabic string

I have a SQL Server 2005 database in which I have some tables contain Arabic text. The datatype for those fields is NVARCHAR(n). The Arabic text inside the table is appearing properly, and when selecting, they appear properly. Th problem is that searching for Arabic text results in 0 rows. select * from table_name where name='arabic_...

sql find duplicate entry and insert into new column

I have a table with columns: Date, Phone, Name, and Event I need a query that will first recognize the duplicate phone entry, and then assign the name of whichever one has the earlier date to the event column. ...

SQL DateTime Datediff

Trying to return the Date stored in the Database in the form Days: Hours : Minutes But the SQL Code below does not seem to work well. select CONVERT(VARCHAR(40),DATEDIFF(minute, MAX(sends), GETDATE())/(24*60)) + '<b>days:</b> ' + CONVERT(VARCHAR(40), DATEDIFF(minute, MAX(sends), GETDATE())%(24*60)/60) + ' <b>hours:</b> ' + C...

how to solve parameter in sp_executesql

Hi, I have the following query: create proc [dbo].GetCustById as DECLARE @sql nvarchar(500) DECLARE @Param nvarchar(200) SET @sql = 'select @columnName from customer where custId = @custId' SET @Param = N'@columnName varchar(10), @custId int' EXEC sp_executesql @sql, @Param , @columnName = 'Address1', @custId = '42' But it always r...

Returning a boolean from a T-SQL Stored Procedure..

What's the most efficient way to return a boolean (true/false) out of a T-SQL Stored Procedure? I want it to do a query and return whether it succeeded or not. I'm calling via ASP. Let me be a little more specific about what I'm doing. If a record exists in a table (indicating a document is already reserved and can't be checked out), I...

SQL queries with views and subqueries

select nid, avg, std from sView1 where sid = 4891 and nid in (select distinct nid from tblref where rid = 799) and oidin (select distinct oid from tblref where rid = 799) and anscount > 3 This is a query I'm currently trying to run. And running it like this takes about 3-4 seconds. However, if I replace the "4891" value w...

SQL query: finding a gap in a primary key

Hi there How can I write a single select statement that does the following: I have an integer column in my table and i want to find the minimum available (non-used) value in that column where the value is below 1000 and also where the value does not exist in TableB Column1 Thanks ...

T-SQL Reverse Pivot on every character of a string

We have a table like below in an sql server 2005 db: event_id staff_id weeks 1 1 NNNYYYYNNYYY 1 2 YYYNNNYYYNNN 2 1 YYYYYYYYNYYY This is from a piece of timetabling software and is basically saying which staff members are assigned to an event (register) and the set of weeks the...

SQL Server 2008 - Task List

Hello. Has anyone ever been able to get the Task List feature in SQL Server 2008 to work. Apparently you are meant to be able to use a comment tag and then TODO, HACK etc as a pointer to that bit of code from the task list. i.e. -- TODO: testing task list /* TODO: testing task list */ If anyone can tell me what I'm missing in ...

SQL: Select MIN value that dosnt already exist

Hi there, In TableA I have an int column. Is it possible using only a select statement to select the minimum value in the column that DOES NOT EXIST and is greater then 0 For example if the col has the values 1,2,9 the select statement will return 3. If the col has 9,10,11 it will return 1 I can achieve this using a temp table or us...

Compare when value could be both NULL or text

Now I know you can't directly compare NULL to anything (as null is unknown) so how would I achieve the following: select * from Material as m where MtrlCode = 826 and Exposlimit <> 'compareMe' Where Exposlimit MAY be NULL or it may not be. 'compareMe' may also be NULL. Therefore how do I compare the two? Bo...

SQL - Order after filtering

How can I order the data and then filter it in TSQL (SQL Server)? I've tried something like this: SELECT [Job].*, ROW_NUMBER() OVER (ORDER BY [Job].[Date]) AS RowNum FROM [Job] ORDER BY Rank WHERE RowNum >= @Start AND RowNum < @End Doesn't work. I also tried to use a subquery, which throws: The ORDER BY clause is invalid ...

How do I return just a portion of a compound key?

I have a sql table with a compound key. 1 field is int, the othe ris varchar. In my stored proc, when I insert a row, I want to use @@Identity to return the id (the int part of this key), but the fact that its a compound key is hosing me. How do I just return 1 field and not the entire key? ...

How do I find which indexes are being used and what query is using the index?

I am using SQL Server 2008. I have tables on which there are duplicate indexes (basically indexes with same definition). I wanted to know if its possible to find out which queries are using these indexes? I don't know why the duplicate indexes were created in the first place. So before removing them, I want to identify any queries which ...

Is there a way to only return a PART of a compound primary key?

I have a SQL table with a compound key, ID (int, autogenerated) and TaxpayerID (varchar, user enters it). When the user registers, they enter a taxpayer ID & the taxpayer ID combined with the ID make a Primary Key. Normally after entering a record, I'd return used @@Identity and return it to a Integer variable in my .NET application. H...