Tag: tsql

Export the result of stored procedure into text file as my computer work as both client/server

I have a procedure which takes as argument the function name and then finds out the full body of that function and stores it in a local variable V_FullString. I need this output not to show in screen rather to put it in text file. Is it possible ? ALTER PROCEDURE [dbo].[SP_DictionaryFunction] @P_FunctionName VARCHAR(100) AS […]

Insert data into temporary table from dynamic query table output

I have the below dynamic query run in SQL Server, connecting to an OLAP server using a linked server, which returns a table as a result. SET @nSQL = EXECUTE (‘SELECT non empty { [Coded Season].[Coded Season].[Coded Season] * [Season].[Season].[Season] * [Product].[Subclass].[Subclass] * [Product].[Subclass Id].[Subclass Id] } ON ROWS,{ [Measures].[Pl No of Range Opts] } […]

SQL: Upsert and get the old and the new values

I have the following table Items: Id MemberId MemberGuid ExpiryYear Hash ————————————————————————— 1 1 Guid1 2017 Hash1 2 1 Guid2 2018 Hash2 3 2 Guid3 2020 Hash3 4 2 Guid4 2017 Hash1 I need to copy the items from a member to another (not just to update MemberId, to insert a new record). The rule […]

TSQL trim columns/table

I am importing to SSMS 2016 txt file (comma delimited, ” text qualifier) but within the text there is a lot of ” which just causing mess. So, I have imported this as comma delimited only, with no errors all looks good. Column(s) look like example: “Ab-123-2 ” “wheel 17″ ” “hello “world” ” Using […]

Count users by create_timestamp and last login date

I want to perform a year by year count of users based upon their create_timestamp and their last_login date. USER create_timestamp Last_login 10 2009-06-18 20161029 11 2010-07-01 20110101 12 2011-10-01 20150101 13 2012-12-01 20161101 Year Count 2009 1 2010 2 2011 3 2012 3 2013 3 2014 3 2015 3 2016 2 Would I be […]

Generic script for finding orphaned data in Microsoft SQL Server tables

I have a Microsoft SQL Server 2012 database with a huge amount of tables (>100). I have also many many-to-many relation tables. All PK/FK relationships are made in the tables so that i know in which table which column belongs to which parent or child tables columns. I wanted to ask if it is possible […]

SQL – Count and group records by month and field value from the last year

I need to count totals number of records in a table, ‘a’, where a field in ‘a’, say ‘type’, has a certain value, ‘v’. From all these records where a.type = ‘v’, I need to group these twice: first by field ‘b_id’, and again by month. The date range for these records must be restricted […]

SQL UPDATE table with OPENXML

I want to UPDATE a table within the following OPENXML statement but don’t know where to put the syntaxes as UPDATE, FROM and WHERE. I get a nice resultset with the OPENXML statement but how do I UPDATE a table with it? DECLARE @x xml SELECT @x=R FROM OPENROWSET (BULK ‘C:\Users\ronal\Dropbox\Projecten\Voormelden Portbase\20161021095457591.xml’, SINGLE_BLOB) AS XMLPortbase(R) […]

SQLCMD to T-SQL (out of memory and scripting error)

I have over 18GB of database .sql file, which have over 200 Millions records. My server have Xeon E5 processor, 16GB ECC RAM and SSDs. I have SQL Server 2014 Web Edition and Windows Server 2012. I know I would need sqlcmd to run huge database so my command is this sqlcmd -S SQL-Server2014 -i […]

Concatenate columns of multiple columns and multiple rows into one varchar value, when no of columns is dynamic

So i have this dynamic query that returns a result set having dynamic number of columns like so: In this result set we have columns for ID, FacilityName and cycleNum will always be there but number of task columns can vary Task1, Task2, Task3….. upto Taskn. The final result set I need is as follows: […]

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