Parse Data from a Field Containing Multiple Values using CROSS APPLY (SQLServerCentral 2019-06-07)

 SQLServerCentral Newsletter for June 7, 2019 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Parse Data from a Field Containing Multiple Values using CROSS APPLY]( - [SQL Server and R with dplyr Package Examples for mutate, transmute, summarise, group_by, pipe and filter]( - [From the SQL Server Central Blogs - Using VALUES to construct an in-line table.]( - [From the SQL Server Central Blogs - Building Your Brand Right]( Question of the Day - [Queue Defaults]( The Voice of the DBA  Microsoft Exercise This editorial was originally published on Apr 27, 2015. It is being republished as Steve is at SQL in the City Christchurch. Have we got a deal for you! Microsoft is concerned about the health of your company and is doing something about it. There's a trend in modern society of obesity, complacency, and general laziness. Not only is this bad for society in general, but it also affects your health. So we have a new solution for you, designed specificially to combat this degenerative lifestyle in an easy way. The Upgrade Treadmill! Designed by the engineers up in Redmond, this highly engineered device ensures that you won't sit around getting stagnant on those junky-technologies and deep-fried solutions. Instead we've built a system of carefully selected products released on a regular cycle that ensures you'll be running for the rest of your career. And it's easy to get started. Just call 1-800-SHARE-PRICE and enroll now in our assurance program. With a short lifetime and quick expiration, our automated reminders to your boss will ensure that you never slack off and slow down your pace of upgrades. It's all in fun, but there's some seriousness here. I originally wrote this awhile back when it seemed Microsoft was pressing for upgrades to SQL Server 2008 R2 after a relatively short development cycle post SQL Server 2008. However the piece got lost in the shuffle. I found it recently and was reminded of the sentiment when I saw [a post to upgrade away from SQL Server 2005 with support ending](. There are good reasons to upgrade; just be sure the reasons are valid for your instance and environment. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](  [Redgate University](  Featured Contents [Parse Data from a Field Containing Multiple Values using CROSS APPLY]( Stan Kulp-439977 from SQLServerCentral.com It is possible for a field in a character-delimited text file to contain a list of further-delimited values instead of the customary single value. This article demonstrates how to load such a file into a staging table, then use a CROSS APPLY query to parse the list of values into a related table. [SQL Server and R with dplyr Package Examples for mutate, transmute, summarise, group_by, pipe and filter]( Additional Articles from MSSQLTips.com In this second part of this tip series we take a look at the dplyr package for R that can be used to query data using the R Language with SQL Server and build data visualizations. From the SQL Server Central Blogs - [Using VALUES to construct an in-line table.]( Kenneth.Fisher from SQLServerCentral This has come up a few times recently, I find it rather fascinating and I can never seem to remember ... Continue reading From the SQL Server Central Blogs - [Building Your Brand Right]( K. Brian Kelley from SQLServerCentral I was interviewed yesterday by Stephen Wynkoop (twitter) for the upcoming SSWUG Virtual Conference on May 23rd. One of the questions was about how to keep management engaged on...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Queue Defaults I create this queue: CREATE QUEUE MyQueue; What are the defaults? Think you know the answer? [Click here]( and find out if you are right.  [Redgate SQL Provision](    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Changing Database Names In SQL Server 2017, how do we programmatically change the name of a database? I want to change database YYY to XXX in a way that will be supported in the future. Answer: ALTER DATABASE YYY MODIFY NAME = XXX Explanation: sp_renamedb is the system stored procedure that will work, but this is deprecated. The ALTER DATABASE command with the MODIFY NAME option should be used. Ref: - ALTER DATABASE - - sp_renamedb - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [SSIS Scale out deployment in a high availability cluster]( - Good day all, Just wondering if it will be possible to have a scale-out master and scale-out worker on primary and secondary in a high availability setup, were in the case of a failover the master on the secondary server would be able to utilize the worker on the primary server and the secondary server, […] SQL Server 2017 - Development [Merge/Split/Switch partition's affect on indexes?]( - Hi All, Currently I have a partitioned table that is partitioned by date. On this table, I have 3 indexes that are not partitioned, each in their own filegroup. I'm now wanting to archive earlier portions of this partitioned data into another table of similar structure; when executing a Merge/Split or a Switch style archive […] SQL Server 2016 - Administration [Buffer Pool calculation]( - Hello Colleagues. Could you tell me about how MS SQL Server 2016 SE determines memory size for Buffer Pool? May be there are a formula ? Thanks in advance. [BPSORT + CXCONSUMER Waits - DEV vs QA server]( - I've got a process which does a lot of ETL type queries which runs fine on our DEV server but not on QA. SQL 2016 developer edition (prod is on Standard, haven't tested there yet) DEV = 4CPU, 16GB sql instance, maxdop = 2 QA = 8CPU, 32GB sql instance, maxdop = 4 On DEV, […] SQL Server 2016 - Development and T-SQL [insert and update]( - Hi, need some advice. I have more than 20 columns and more than 50k rows. I would like to do insert and update. I did the merge in SQL from this example: I am having issue where it takes too long. More than 20 minutes. Is it because of the total rows? Any other […] [Determining the length of a data type]( - On this website in at the beginning of the 3rd paragraph, we have: "Length for a numeric data type is the number of bytes that are used to store the number." When I run this code, the answer is 2. DECLARE @n int SELECT @n = 25 SELECT LEN(@n) The int data type has 4 […] Administration - SQL Server 2014 [Reporting Server]( - I have reporting server which gets replicated from other OLTP server.However, the reporting server has low PLE and hig disk queue which make sense having low PLE could lead to high disk queue length and this trigger alerts. However, adding more memory on reporting server would help but it's reporting server so it would take […] [FlushCache Error Message]( - While analyzing an issue, I stumbled on to a "FlushCache" error message (error log). There were many (both AM + PM). And this had been going on for quite a while. Had not seen this message before. First instinct was to check other production SQL Servers (VMs) to see if this was happening else where. […] [StartDate & End Date parameters]( - Hi, I've created a view to populate data from existing tables In my report project I built using SSDT 2014, I created Dataset to read form this view. I also set a Startdate and Endate parameter with default calander values. The expectation is for the dates to filter the dates by above parameters. The Report […] SQL Server 2012 - T-SQL [Need help with query where i need to aggregate an aggregate]( - Hi all, I have an inherited system which I can't do much with so (for now) i'm stuck with what i've got. Data is collected and inserted into a "storage" table at various times throughout the month which shows customers and how much storage they are consuming. I need to report on the TOTAL maximum […] SQL Server 2008 - General [How to map the table data while inserting into another table in sql]( -  table1 id  value 2  20 3  30 4  40 5  50 6  60 7  null the above table has to be inserted into another table2 with following fashion i,e: it has to insert the value corresponding to 2 into col1 when id =2 ,col2 when id=3 and so […] [How to map the table data while inserting into another table in sql]( - table1 id  value 2  20 3  30 4  40 5  50 6  60 7  null the above table has to be inserted into another table2 with following fashion i,e: it has to insert the value corresponding to 2 into col1 when id =2 ,col2 when id=3 and so on. […] [How to map the table data while inserting into another table in sql]( - table1 id  value 2  20 3  30 4  40 5  50 6  60 7  null the above table has to be inserted into another table2 with following fashion i,e: it has to insert the value corresponding to 2 into col1 when id =2 ,col2 when id=3 and so on. […] Powershell [run stored procedure from PS script]( - anyone have a script that will run a PS script to run a SQL SP and catch if it fails along with capture messages from the SP? Thanks Integration Services [Import data from Word Custom Controls]( - I'm just wondering how hard this would be in SSIS... I got it to work in Access using Word automation/VBA (dirty word, I know!) I can loop through the Word file's ContentControls collection and map the names to fields in my table. Has anybody done this in SSIS? The lack of branching in Access is […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -