tsql

TSQL table based function returning different tables

Hi, I am triyng to write a table based fuction so it returns a different results depending of the value. I have: CREATE FUNCTION [dbo].[tblfn_GetAnyDataSet_As_View] (@DataType as varchar(50)) returns table asreturn select * from (select * from table1 UNION select * from table2) DATA where DATA.DataType=@DataType and I want to up...

Combining 2 TSQL statements into 1

Hi, I need to combine the following two SQL statements into one. Any help is greatly appreciated. Thanks. SELECT C.*, M.members_Email FROM tbl_Campaigns C JOIN tbl_Members M ON C.campaign_MemberId = M.members_Id WHERE C.campaign_MemberId = @userID ORDER BY C.campaign_Key DESC SELECT COUNT(*) FROM tbl_CampaignRecipients WHERE reci...

Users have open files on BackupSharedFolder. Continuing the operation will force the files closed.

I am using below sql statement to create shared folder with permission EXEC xp_cmdshell 'net share BackupSharedFolder=D:\testshared /REMARK:"test shared"' It is working fine with create shed folder. then i will use below sql command to remove shared EXEC xp_cmdshell ' net share BackupSharedFolder /delete"' Above is also working fine ...

Any site out there that let's me run test SQL queries on sample databases?

You wanna test a GROUP BY on some sample data in the browser without having to install a DB engine and you don't have a remote access to any cloud database? Yeah, me too. Let's say it is for educational purposes. MSSQL or MySQL flavoured SQL would be nice. ...

Diff b/w time in two rows in sql server

hi, i have a sitaution where i need to compare time in two diff rows,its like this categoryid item_id group date 1 10 abc 2008-03-07 03:02:00 1 35 bcd 2008-04-03 10:03:00 2 13 cde 2008-03-13 07:18:00 2 40 ced 2008-03-13 08:41:00 2 44 cef 2...

Optimising DATEDIFF

I just want to check my logic there. Suppose I want to find all new products in the last 30 days. My current procedure is this: SELECT ProductName FROM ProductTable WHERE DATEDIFF( d, CreateDate, GETDATE() ) < 30 However, I understand that functions like DATEDIFF will not use the non-clustered index I have created on CreateDate. So t...

how to query to create a grouping of sets

I have a normal joining table with two columns that are keys to other tables. The records are customers mapped to a range of assets. asset_map ----------- customer_id asset_id ----------------------- 1 1 1 2 2 1 2 2 3 1 3 2 3 ...

Split string returning a record

Hi, I have a single column table which looks like this: Gaming | Austria | 47.9333, 15.1 Hebei | China | 39.8897, 115.275 This means that every row is a single string (VARCHAR) that contains some location, with the different fields separated by a pipe (|). I would like to write a query that returns the following: ------- ----------...

The INSERT statement conflicted with the FOREIGN KEY constraint

Error: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Item__order__3AE27131". The conflict occurred in database "pmall", table "dbo.ItemSaved", column 'id'. Here's my table: ItemSavedUnits id ItemID (is set in this table a FK to Item.id) ...etc. Here's my insert stat...

How to merge XML in T-SQL?

It doesn't seem that any amount of reading the docs will help me. Consider the simplified example: declare @table1 table ( id int, parent xml ) insert @table1 values( 1, '<Root></Root>' ) declare @table2 table ( id int, guts xml ) insert @table2 values( 1, '<Guts>hi mom!</Guts>' ) select t1.parent.query('') from @table1 t1 inner join ...

I really really want to use an INSERT nested in a SELECT

I know this is a task that can't be unique, but I can't seem to find a way to do what I'm thinking in my searches and reading, so if it's not possible, I'd like to know if I'm choosing the best alternative. Simple case of moving information on people from the old database to the new. The problem is that the old schema has the address on...

How to count same Id in a column in a table and based on count perform concatenation.

I have a table in below format ID Name 1 Amit 2 Mohit 2 Nahar My reqiurement is to get a query that will update this table to below format ID Name 1 Amit 2 Mohit OR NAHAR Is there any query in SQL that can solve this purpose? ...

A question about desining a faster algorithm in T-SQL for a CASE that I will be telling.

Hello people. Here is the CASE I have an sql table consisting of just one data column, consisting of some group names. Here how the table looks: OPEN SYSTEMS SUB GR OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(2222)/BTVY(4444)/ACSVTYSAG INFRASTRUCTURE SOFT SUB GR INFRASTRUCTURE SOFT SUB GR (GM/BTIB(1111)/BTUGBL(3333)/BTUGBL(3333)/BTAUS...

Selecting group using Unknown column value

hi guys, I have a large table with records like (id,name,Class) I want to be able to do a operation "Class" wise .. but i dont know what are all possible values for Class Currently i have to do is use 2 queries : Query 1: result = select distinct class from myTable; Query 2 : for each value from result , classWiseRows = select *...

Prefix all columns in T-SQL statement

Hi Given a table "ABC" with columns Col1, Col2 and Col3 it is possible to write the following SELECT Col1 AS 'ABC_Col1', Col2 AS 'ABC_Col2', Col3 AS 'ABC_Col3' FROM ABC This is ok, but i have a table with a not fixed set of columns (users are able to append their own columns) where I still need the column prefix (because it is need...

SQL Server 2000 T-SQL: writing to MSMQ?

Is there a way to write to MSMQ from T-SQL and/or Stored procedure? I am using SQL Server 2000. ...

SQL Server slow select from large table

Hello, I have a table with about 20+ million records. Structure is like: EventId UNIQUEIDENTIFIER SourceUserId UNIQUEIDENTIFIER DestinationUserId UNIQUEIDENTIFIER CreatedAt DATETIME TypeId INT MetaId INT Table is receiving about 100k+ records each day. I have indexes on each column except MetaId, as it is not used in 'where' clause...

Are delimited identifiers considered a "best-practice" in Transact-SQL?

I'm working on some legacy SQL and the author delimited every column name and data type declaration. See the following: CREATE TABLE SomeTable ( [SomeDate] [datetime] NOT NULL, [SomeInt] [int] NOT NULL, [SomeString] [nvarchar] NOT NULL ) ON [PRIMARY] GO Is this considered a best-practice when writing T-SQL for S...

CTE error: "Types don't match between the anchor and the recursive part"

I am executing ;with cte as ( select 1 as rn, 'name1' as nm union all select rn+1,nm = 'name' + CAST((rn+1) as varchar(255)) from cte a where rn<10) select * from cte and is receiving the error Msg 240, Level 16, State 1, Line 2 Types don't match between the anchor and the recursive part in column "nm" of recursive query "cte". Wh...

Why “Set based approaches” are better than the “Procedural approaches”?

I am very eager to know the real cause though earned some knowledge from googling. Thanks in adavnce ...