tsql

SQL Server 2005: T-SQL INSERT INTO and OUTPUT timestamp to a variable

Example: IF OBJECT_ID('T1') IS NOT NULL DROP TABLE T1; GO CREATE TABLE T1 (id int PRIMARY KEY, timestamp); GO INSERT INTO T1(id) VALUES (1); GO declare @v timestamp; INSERT INTO T1(id) OUTPUT inserted.timestamp as v VALUES (10); select @v How can I get the inserted.timestamp into variable @v? ...

return records in sql server which has data in a particular format

Hi Guys, I have a SQL database table column which has data in this format "0000-00-0000" for ex: "1234-98-2010" For it has data in other formats as well. I need to pick out all the records where the format is "0000-00-0000" irrespective of the data. Its the format which i am after Please, can some one advise me on how i can pick th...

update sql column with same data but with a little modifications to it

I have a sql DB table columns which carries data in "0000-00-0000" format. For ex: "8753-11-2010" Now i need to change this value from "8753-11-2010" to "008753-0011-2010" i.e. i need to pad "00" in front of "8753" and "11" i mean the first two strings u can call it. please advise how i can achieve this in sql server 2005. I need to ...

I want to show records from in 1 row.

Table 1 (history data) SiteName OutcomeType SpeciesType Count DateType ------------------------------------------------------------- S1 Adopted Dog 3 0 S2 Adopted Cat 12 0 S1 Transferred Puppy 2 0 S1 Transfer...

Table in SQL Server won't return more than a few thousand rows

Using SQL Server 2005. I was doing some simple queries on a table that has about 200k records. As of today when I got to work, a simple SELECT * FROM executes till it retrieves about 20k rows...then stops. It won't go past 20k rows. If I try to select just ONE row while using ORDER BY Created DESC, the query runs indefinitely. I've neve...

Aggregate bitwise-OR in a subquery

Given the following table: CREATE TABLE BitValues ( n int ) Is it possible to compute the bitwise-OR of n for all rows within a subquery? For example, if BitValues contains these 4 rows: +---+ | n | +---+ | 1 | | 2 | | 4 | | 3 | +---+ I would expect the subquery to return 7. Is there a way to do this inline, without creating a UDF...

Finding matches in multiple columns in different orders

Hello, I am trying to merge 2 databases with the same schema together, and this is one part of it. I have changed the subject to keep it more understandable - I cannot change this schema, it's just what I'm working with. I have a table in both my source and target databases with the following columns: Car CarType1 CarType2 CarType3 C...

How to create sequential number column index on table with data

Hi I have the following table with 10 unique rows, BookingID is a FK containing a random number. The number doesn't need to be in sequence. BookingID, Description 1000 Foo 3000 Bar 1500 Zoo I need to insert an sequential index called ID which goes from 1..x how do I do that in SQL Server 2005? I was thinking to wri...

Where is the Query Analyzer in SQL Server Management Studio 2008 R2?

I have some SQL thats getting run and it is taking to long to return the results / parse / display, etc. in a asp.net c# application. I have SQL Server Management Studio 2008 R2 installed to connect to a remote SQL Server 2000 machine. Is there a Query Analyzer or profiler I can use to see whats going on? I'm not sure if I'm sending too...

'Where in' T-SQL implementation

Hi guys, I have a stored procedure that take many input parameters including an @userID. Now, in the core of the SP I select from a view using the following Where close : Where userID = @userID This works fine. Now I want to pass multiple users to be able wo query it like this : where userID in (1,2,...) How can I pass the value ...

Select last value in a month for all given IDs

I have 2 tables, one containing meter IDs, and another containing measurements for some of the meters in the first table. This is the table structure: MeterConfig: MeterID (int) MeterNumber (char[16]) Type (char[25]) Readings: MeterID (int) Date (datetime) Value (numeric(18,6)) I need to get the last reading (and its date) from ...

Pivoting SQL Server 2005 for unknown number of rows

My table is a dynamic one. E.g.: id SUBJECT 1 his 2 math 3 sci 4 opt 5 ENG 6 SOC The number of rows is not limited. There could be a hundred. I want output like this: ID 1 2 3 4 5 6 HIS MATH SCI OPT ENG SOC I could use a pivot query, but I would have to know the number of colum...

Using t-sql to select a dataset with duplicate values removed

I have a set of tables in SQL Server 2005 which contain timeseries data. There is hence a datetime field and a set of values. CREATE TABLE [dbo].[raw_data]( [Time] [datetime] NULL, [field1] [float] NULL, [field2] [float] NULL, [field3] [float] NULL ) The datetime field is unfortunately not a unique key, and there appea...

SQL - ordering a common table expression

Using this sample table: drop table Population CREATE TABLE [dbo].[Population]( [PersonId] [int] NOT NULL, [Name] [varchar](50) NOT NULL, [MotherId] [int] NULL, [FatherId] [int] NULL ) ON [PRIMARY] insert Population (PersonId, [Name], MotherId, FatherId) values (1, 'Baby', 2, 3) insert Population (PersonId, [Name], Mothe...

To check on NULL or 0

What is faster in SQL to check value for NULL or 0 I want to have the fastest way to check is value already in table. For example which is faster : IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] WHERE VALUE = @myVal) is null ) BEGIN .... END ELSE BEGIN .... END or IF ((SELECT ID FROM [SomeTable].[dbo].[Bloc...

Exiting from recursive common table expression once the result set contains some value

Given the following table: create table TreeNode ( ID int not null primary key, ParentID int null foreign key references TreeNode (ID) ) How could I write a common table expression to start at the root (WHERE ParentID IS NULL) and traverse its descendants until the result set contains some target node (e.g., WHERE ID = n)? It's ea...

sql server rollback...

Possible Duplicate: SQL Identity (autonumber) is Incremented Even with a Transaction Rollback does a rollback also rollback identity values??? ...

Convert Xml to Table SQL Server

I wonder how can i read a xml data and transform it to a table in TSQL? For example: <row> <IdInvernadero>8</IdInvernadero> <IdProducto>3</IdProducto> <IdCaracteristica1>8</IdCaracteristica1> <IdCaracteristica2>8</IdCaracteristica2> <Cantidad>25</Cantidad> <Folio>4568457</Folio> </row> <row> <IdInvernadero>3...

sql resultset records numbering ?

I want to number the rows in my result set. I want some way I can have a result set of 3 records use some SQL keyword to generate a column that would read 1,2,3 for watch of the records... I know I can make a temp table with an auto increment column but i wanted to know if there was a way I can get this back from a SQL query? SELECT r...

Regex for a serial number in T-SQL

I'm a novice at regexs and am currently trying to come up with a simple regex that searches for a serial number in the following format: 0217103200XX, where "XX" can each be a numeric digit. I'm using SQL Server Management Studio to pass the regex as a parameter in a stored procedure. I'm not sure if the syntax is any different from ot...