Tag: sql-server-2012

How to return a table variable from a function (UDF)?

I’m using SQL Server 2012 and I have been trying lots of different approaches to return a table variable from inside a function, but I cannot get it to work. I’ve tried moving the variable declaration to different places, etc. Here are the guts of the sql. If you could please wrap the guts in […]

INSERT statement cannot contain a SELECT statement -sql server2012

declare @us table(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit); declare @Name nvarchar(100), @lastname nvarchar(100), @UID bigint, @Avail bit insert into @us select @name=name,@lastname=lastname,@UID=UID,@Avail=available from Users where available=’1′ select * from @us I got this error An INSERT statement cannot contain a SELECT statement that assigns values to a variable. I searched for this problem but many people […]

sql server running total with over, partition

I am trying to calculate running total in following query select a.ICode, MONTH(a.VDate), YEAR(a.vdate) , sum(isnull(a.qty, 0)) , sum(isnull(a.qty, 0)) OVER (partition by a.icode order by a.icode) AS ‘total’ from t_Stock as a group by a.ICode, MONTH(a.VDate), YEAR(a.vdate) order by a.icode, YEAR(a.vdate), MONTH(a.VDate) but I am getting an error: Msg 8120, Level 16, State 1, […]

Select non-empty columns using SQL Server

I am using SQL Server 2012. i have a table with 90 columns. I am trying to select only columns that contains data. After searching i used the following procedure: 1- Getting all columns count using one select query 2- Pivoting Result Table into a Temp table 3- Creating Select query 4- Executing this query […]

Conditional join to two different tables based on 2 columns in 1 table

I have a table that looks like this dbo.Box ID SourceID OverrideQueueID 1 1 NULL 2 1 2 3 2 NULL I need to figure out a way to say if the OverrideQueueID IS NULL then just do a join from dbo.Box to dbo.Source.ID, otherwise if OverrideQueueID IS NOT NULL join to the dbo.Queue.ID instead. […]

Group and order with additional exception

I have following table: Declare @YourTable table ([Event] varchar(100),[Start] DateTime,[End] DateTime, [Tag] varchar(25)) Insert Into @YourTable values (’10PIC700422.PV 10-PSV-700073A 10-PSV-700073B’,’9/9/16 10:44′,’9/9/16 10:49′,’Big’), (’10PIC700422.PV 10-PSV-700073A 10-PSV-700073B’,’9/9/16 10:50′,’9/9/16 10:51′,’Small’), (’11PIC41010.PV 11-PSV-401002A 11-PSV-401002B’,’4/4/16 12:51′,’4/4/16 13:58′,’Big’), (’11PIC41010.PV 11-PSV-401002A 11-PSV-401002B’,’4/4/16 14:04′,’4/4/16 14:29′,’Small’), (’11PIC41010.PV 11-PSV-401002A 11-PSV-401002B’,’4/4/16 14:51′,’4/4/16 14:58′,’Big’), (’11PIC41010.PV 11-PSV-401002A 11-PSV-401002B’,’4/4/16 15:04′,’4/4/16 15:29′,’Small’), (’11PIC41010.PV 11-PSV-401002A 11-PSV-401002B’,’4-4-16 15:45′,’4-4-16 15:55′,’Big’), (’11PIC41010.PV 11-PSV-401002W 11-PSV-401002D’,’4/4/16 […]

Update row base on distinct, stuff, path query

Continue from Combine multiple rows into one "memo" nvarchar(max) SELECT DISTINCT fileref, stuff( ( Select char(13)+char(10) + IsNull( D2.viaccno, ” ) From vi_accno As D2 Where D2.vifileref = A.FileRef For Xml Path(”), type ).value(‘.’, ‘nvarchar(max)’) , 1, 2, ”) As accno FROM vtindex a How do i use the above query to update each accno […]

Create copy of database in sql server 2012 express

Hello I have database on which user work. I want to create copy of this database for testing purpose. Can anyone tell how can this be done? I tried everything from copy a database within SQL Server Express? But Copying files and attaching it destroys first database and trying to restore backup into new database […]

maintaining a custom auto increment column

I have a table with ID (identity) and XID (int) which is my custom auto increment column. I am using an instead of insert trigger to maintain XID, but I am getting duplicates. table xtable (ID identity, XID int) trigger – instead of insert insert into [xtable] (XID) select [x].[NextavailableID] from inserted [i] cross apply […]

Combine multiple rows into one “memo” nvarchar(max)

2 tables 1 with clients other with accno information table 1: file, accno <- nvarchar(50) t1, 123a t1, 456a t1, 789a t2, 012b t3, 345c t3, 678c i would like to transfer table 1 to table 2 table 2: file, accno <- nvarchar(max) t1, 123a 456a 789a t2, 012b t3, 345c 678c i can do […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.