A Simple Method to Load Excel Data (2024-01-15)

SQLServerCentral Newsletter for January 15, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [A Simple Method to Load Excel Data]( - [Eager Aggregation in SQL queries]( - [From the SQL Server Central Blogs - Changing the Origin in Git–#SQLNewBlogger]( - [From the SQL Server Central Blogs - Power BI Embedding: Essential Learning Resources]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [Creating Tables without a Schema - II]( The Voice of the DBA  Portable Data Storage I saw [a review on a rugged SSD]( and it got me thinking about local storage. Over the holidays I needed to scan something from a device at the ranch, but to get the image to my computer, I needed a flash drive. It took me 10 minutes of searching around to find one because I rarely use them anymore. In fact, I couldn't remember the last time I'd used one for a transfer between computers. I just send stuff through OneDrive/DropBox/GDrive or some other mechanism. Today I'm wondering if the rest of you have abandoned using portable storage or if is it something you find useful. How often do you use portable storage in your life? Apart from laptops or mobile phones, I'm wondering about portable disk drives or any device where you transfer data using a wire rather than wirelessly. Note, I'm not talking about going from one computer to another on a wired network. I mean moving the storage from one place to another with your hands. The only place I know I'm regularly using a portable drive is in the Tesla. There I have a rugged portable SSD that captures the dashcam and security footage. I [had to transfer it from the drive]( to my computer last year when I saw an accident. I think that might have been the last time that I actually used portable storage (before the scan). I have lots of old portable drives, and even old CD/DVDs I've recorded that have SQL Server ISO, pictures, backups of databases, and more. However, I don't use them anymore. I don't know how many of those devices still work, and I ought to check them, load the backups into cloud storage, and then recycle (after erasing) the devices. Or trashing them. I'm guessing many of them are just old tech at this point no one wants. I think the era of using portable storage most of the time is likely gone. Even my friends who like to keep their own backups locally tend to use a NAS of some sort and wireless transfers, as opposed to plugging in. However, maybe I'm wrong. Are some of you still Luddites with storage technology, if that word can even apply to digital storage technology? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [A Simple Method to Load Excel Data]( Louis Bravo from SQLServerCentral Learn how a C# script can easily load data from Excel into SQL Server. [External Article]( [Eager Aggregation in SQL queries]( Additional Articles from SimpleTalk Aggregation is a widely used way to summarize the content of a database. It is usually expressed with GROUP BY clause or just using aggregate functions (like COUNT or SUM). When the database engine executes a query with aggregations, it produces individual rows need to compute the required output and then performs the aggregation as (almost) last step. We discuss in this article how to re-write a query manually so that the order of operations will be different and when it can be beneficial. [Blog Post]( From the SQL Server Central Blogs - [Changing the Origin in Git–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA I needed to show a customer how to migrate from Azure DevOps to GitHub recently, and to smooth this process, we needed to repoint the origin remote. Another post... [Blog Post]( From the SQL Server Central Blogs - [Power BI Embedding: Essential Learning Resources]( Joyful Craftsmen from Joyful Craftsmen Blog Introduction In this blog, you will find valuable resources to help you better understand the Power BI Embedding feature. Whether you are looking for ways to integrate Power BI into... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  Creating Tables without a Schema - II I run this code: CREATE LOGIN JoeDBA WITH PASSWORD = 'DemoP@sswordTh@t1sLong' GO ALTER SERVER ROLE sysadmin ADD MEMBER JoeDBA GO USE sandbox GO CREATE SCHEMA etl GO CREATE USER JoeDBA FOR LOGIN JoeDBA WITH DEFAULT_SCHEMA =[etl] GO Then I log in as JoeDBA and run this: USE sandbox GO CREATE TABLE ANewTable (newid INT) What table is created? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Rounding Numerics I have this code on SQL Server 2019: SET ARITHABORT ON; GO DECLARE @result DECIMAL(5, 2), @value_1 DECIMAL(5, 4), @value_2 DECIMAL(5, 4); SET @value_1 = 1.1234; SET @value_2 = 1.1234 ; SELECT @result = @value_1 + @value_2; SELECT @result; GO What setting of NUMERIC_ROUNDABORT will return an error from this code? Answer: SET NUMERIC_ROUNDABORT ON Explanation: When this is set to OFF, an error is returned because of the loss of precision. Ref: NUMERIC_ROUNDABORT - [ [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 [How to optimization of a stored proc]( - Hi All, Need some help on optimizing a stored proc. I see some of the update statements written multiple times. Instead of writing multiple updates , can we re-write in one single statement which might avoid reading same table multiple times. This stored proc is doing a lot of logical reads almost like 76TB of […] SQL Server 2017 - Development [Grouping based on chronological periods]( - Hello, I have a table Customers that contains the current value and another table Customers_History contains the history values. The customers have more that 10 fields AS Bellow a sample of data : CREATE TABLE #CUSTOMERS( ID int primary key identity, First_Name varchar(255), Last_Name varchar(255), Nick_name varchar(255), Email varchar(255), Date_Bithday date, Gender varchar(1), Is_Married int, […] [Null value is eliminated by an aggregate or other SET operation]( - Should I see this warning when I have ISNULL check at SUM function and changing NULLs to 0?  Thank you SQL Server 2012 - T-SQL [parse JSON formatted text into M3U Playlist]( - sorry.. solved.. [Parse out filed as date time ???]( - Hi  I have a field with a start and end date (sometimes no end date) . How can I parse this out to have two fields start and end dates? Data looks like ~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~ desired out is Start Date 12/14/2019 7:02 AM End Date 12/14/2019 3:16 PM  Thanks  SQL Server 2019 - Administration [SQL 2019 data masking]( - I have setup data masking on several fields and can see that the data is masked using t-sql. I need this masking to follow through to report server. I am not seeing the data masked there. any ideas / suggestions would be greatly appreciated SQL Server 2019 - Development [ReportViewerControl.exportReport opening in new Window/Tab]( - Hello, I am using the ReportViewer.aspx page to display a SSRS Report. When exporting the report to any format(CSV,PDF,Excel, etc.) it uses the ReportViewerControl.exportReport function which has the following code: window.open(this.ExportUrlBase+encodeURIComponent(n),"_blank") What I would like to do is stop a new window/tab from being created, so instead of "_blank", "_self" could be used. Any idea […] Reporting Services [copy or duplicate a folder in SSRS web portal]( - Is there a way to copy a folder in SSRS 2019 server web portal to another folder, I want to make a copy of backup for temporary keep. For example I have a folder called myReports which has 20 reports in it, I want to duplicate the folder and all the reports in it to […] Analysis Services [SSAS tabular account impersonation]( - Hello, I'll try my best to summarize the problem and I hope someone can offer me a hand with this. The Enviroment: 2 Domains (let's call them domain_1 and domain_2) Server1 (it is joined to domain_1) This server has SQL Server and SSAS SSAS has a domain account set up on the service Domains have […] Integration Services [On Error Event handler in For Each Loop Container]( - I have a For Each Loop Container that iterates through a list of servers in my environment. Usually it is used to gather information from each of the servers. Inevitably there are a few errors. But my error handler does not write the name of the Server at which the error occurred. Here is my […] [Visual studio 2019 hangs when open excel data source]( - I had Visual studio 2019 installed on windows 10. I have a SSIS package that has been working when I run it in visual studio. But recently when I open the ssis package, and open the excel data source to edit, it freeze there forever, then visual studio shows busy. I have to hard stop […] SQL Server 2022 - Administration [Scheduling AWS RDS instance resizing]( - I know that it is possible to use the AWS Scheduler to stop and start RDS instances. For my purposes we just want to resize it e.g. 16 to 4 processors, so it can still work in low usage periods but will not cost so much. Has anyone ever done anything like this? We do […] SQL Server 2022 - Development [How to query selected multiple rows into multiple colums]( - How to display datas using query data from multiple specific rows in one table sql into multiple columns? For example, insert red data into column1, insert blue data into column2, insert green data into column3, etc. I've used these code : SELECT um_value FROM wp_um_metadata WHERE um_key='first_name' But the result I only got one column. […] [Column Name Is Different in Power BI Than SQL View]( - Hi all, Using Snowflake. When I Describe my View the first columns shows as Nielsen Market Name (KUSA Adjusted) VARCHAR (10000) I connected to the View from Power BI Desktop (PBID) However, in PBID, the name of the column is showing as Nielsen Market Name (USA Adjusted) - the "K" has been dropped from the […] [Basic query performance]( - Hi, I have this pretty basic query that is starting to perform slowly. It returns over 22m records in about 5 minutes. I know its difficult without table defs but any ideas on what I can try to make this return faster?  SELECT [e].[eligibility_id] FROM [dbo].[eligibility] AS [e] -- Table is updated hourly with […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -