tsql

Determining SQL data path for DB RESTORE with MOVE

I'm trying to write a script which automatically restores a database backup. I know I can use the following RESTORE command: RESTORE DATABASE [DBRestoredName] FROM DISK = N'C:\path\to\backup.bak' WITH FILE = 1, MOVE N'DBNAME' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.mdf', MOVE N'DBNAME_log' TO...

TRANSACT SQL - How to manually order/index a table based upon a select/where statement

Hello all, I have sql puzzler today. I have the following sql : SELECT MemberId, UnitId, MaterialLevel, MaterialText, ProductPercentage, ProductPriority FROM tblWeights ORDER BY MemberId, MaterialLevel, MaterialText, ProductPercentage DESC Now, currently the ProductPriority is e...

Giving conditions in SQL SELECT statement

Hi All, I have to run an SQL query based on conditions. There are 2 AND conditions that needs to be executed only if the if conditions for them are satisfied. Can we use CASE statement here. If so how? Or are there any other methods?? SELECT * FROM MyTable WHERE col1=@val1 if condition1 here AND col2 = @val2 end if if condition2 her...

Two Rows Get Inserted with One INSERT statement?

Hello all, How is it possible that this function below inserts two rows in my database? //called like save_session_db($_SESSION['user_id']); function save_session_db($session){ include('includes/db-connect.php'); $connectionInfo = array( 'Database' => 'Belior'); $conn = sqlsrv_connect( $serverName, $connectionInfo); if...

Change Sql "Not In" To "Left Outer Join"

Hi, I don't want to use "not in" this sql query. How can I do it? thanks SELECT T2.Sno, T2.Name, T1.description, T2.UserCode FROM Table1 AS T1 (nolock) INNER JOIN T2 (nolock) ON T1.UserCode = T2.UserCode WHERE g.xid= @p_xid and T2.Sno not in (select Gid from T3 (nolock)) ...

How do I efficiently group data in a hierarchical format in T-SQL?

I have data like this: Task | Hours 1.1 | 40 2 | 40 2.1 | 60 2.1.1 | 15 15.9 | 24 16 | 5 19.1 | 40 19.1.1 | 8 19.1.2 | 12 19.2 | 6 19.2.1 | 21 19.2.2 | 15 19.2.3 | 2 19.3 | 64 I would like to group based on the first two levels of the Task, producing this result...

Please tell me how to get primary key name in SQL Server 2005

I need a primary key name and primary key column name of a table please tell me what query should I write.. ...

Sql Syntax: Update All values in table based on a value in a different table

Hi, I have three tables and I want to update all values for a particular type to the same value: table1: id, ValueType table2: id, Value table3: id, fkValueTypeId, fkValueId fkValueType references ID in table1. fkValue references ID in Table2 I am trying to set all Speed values to the same value: i.e. Table1: 0, speed 1, age 2, c...

What is wrong with this nested WHILE loop in SQL

I ran into a weird situation today doing some one-time sql code. This nested loop doesn't seem to run the outer loop: it prints (0,0),(0,1),(0,2) and (0,3) declare @i int, @j int select @i = 0, @j = 0 while @i < 3 begin while @j < 3 begin select @i as i, @j as j set @j = @j + 1 end set @i = @i + 1 end Am I ...

Trying to audit deletions with a half baked system.

My ERP system has a half baked deletion tracking system which inserts the following info into a table called M2MDeleteLog. I have left out unnecessary columns such as RecordId for simplicity. LogDate Workstation LogInfo 1/7/2010 11:01:51 TECH-M2MTEST Deleting 1 Rows From SOMast 1/7/2010 11:01:51 TECH...

Recursive t-sql query

Hi I have a table as shown below. ID ParentID Node Name Node Type ------------------------------------------------------------------ 525 524 Root Area Level 1 526 525 C Area Level 2 527 525 A Area Level 2 528 525 D Area Level 2 671 525 E Area Level 2 660 527 B Area Level 3 672 671 F Area Level 3 How can i write ...

Plain SQL vs Dialects

DBMS Vendors use SQL dialect features to differentiate their product, at the same time claiming to support SQL standards. 'Nuff said on this. Is there any example of SQL you have coded that can't be translated to SQL:2008 standard SQL ? To be specific, I'm talking about DML (a query statement), NOT DDL, stored procedure syntax or anyth...

de-duplicating rows in a sql server 2005 table

I have a table with ~17 million rows in it. I need to de-duplicate the rows in the table. Under normal circumstances this wouldn't be a challenge, however, this isn't a normal circumstance. Normally 'duplicate rows' is defined as two or more rows containing the exact same values for all columns. In this case 'duplicate rows' is defined a...

T-SQL escape quote character

NOTE: It's probably a duplicate but I can't find working answer. Following is what i'm trying todo, notice a ' in the value. How do I fix this? INSERT INTO [pugraider].[dbo].[Realms]([Name]) VALUES('Aman'Thul') I use MS SQL Server Management Studio 2008. EDIT: I'm writing a script to populate a lookup table (ID<->Name). ...

PHP SQL Server Database Select

I have three SQL Server databases on one SQL Server instance. Every time I do query to different databases, I have to select the database. Is there a way to do query without database select. Like this: SELECT * FROM database_name.table_name WHERE id = 1 ...

Give an example of Recursive CTE

Thanks in advance ...

How to truncate the table value parameters (TVP) in TSQL?

I want to truncate the TVP instead of DROPPING. When I am writing TRUNCATE TYPE it is giving error. DROP TYPE is working but iI want TRUNCATE Can any one help me with the syntax? thank you ...

SQL Query to find earliest date dependent on column value changing

I have a problem where I need to get the earliest date value from a table grouped by a column, but sequentially grouped. Here is a sample table: if object_id('tempdb..#tmp') is NOT null DROP TABLE #tmp CREATE TABLE #tmp ( UserID BIGINT NOT NULL, JobCodeID BIGINT NOT NULL, LastEffectiveDate ...

TSQL: union results from two selects (procedures?)

I have two procedures - two huge sets of selects with several sub-select and unions. I need to union results from these procedures and I still need them to exists separately. Something like that: if @Param = 1 Then PROCEDURE1 if @Param = 2 THEN PROCEDURE2 if @Param = 3 Then PROCEDURE1 union PROCEDURE2 I read that it's i...

SQL Server Geography datatype nearest point on line

I am trying to build a query, but I am having some difficulty. I have a SQL Server 2008 database with a table that includes, among other fields, a geography field that describes road segments. (This data has been imported from TIGER/Line data from the U.S. Census.) I have another fixed point describing a user's location. I want to find...