PostgreSQL Copy (2023-06-09)

SQLServerCentral Newsletter for June 9, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [PostgreSQL Copy]( - [Overcoming Organization Challenges in Cloud Migration: A Webinar for Senior IT Leaders]( - [Data Transfer from Snowflake to Azure Blob using Synapse Analytics]( - [From the SQL Server Central Blogs - Working with ALS – ALS Awareness Month 2023]( - [From the SQL Server Central Blogs - Stacked Bar Charts vs. Line Graphs–Which is Better?]( - [Expert Performance Indexing in Azure SQL and SQL Server 2022]( Question of the Day - [Secure Enclave Attestation]( The Voice of the DBA  What's Your Job? There are a lot of technology people looking for jobs these days, especially after all [the layoffs that have occurred in 2023](. At the same time, I have a number of friends and clients that are struggling to hire qualified people. They get lots of candidates, but they are dismayed at how little they seem to know, at least in the areas that their organization has needs. Today, I want to help people get better at growing and managing their careers. Therefore I'm asking those of you that are employed to describe your job. Give us your title in the comments and then list 5-10 things that you've done lately. What types of queries have you written? What tasks did you complete with SQL Server or Azure SQL? Have you needed to research something to solve a problem? What knowledge or concepts helped you complete a task? The idea is to share some of the skills or concepts a person should have to grow their career. Hopefully, some people will then read descriptions and build skills. As an example, I'll give a few tech things I've seen lately from clients. These are practical things someone needed to finish, or something they asked someone else to do. I was working with a client and there was a need to find all the logins whose passwords were not set to expire. How can you do that? A table had a lot of duplicate data, and all duplicates but the row with the latest date needed to be deleted. What does the DELETE statement look like? Caveat, what if I need to update all but the first row with a new value in some column to mark them as inactive? A client needed to get a list of all the servers from their central management server (CMS) and then extract that last as a CSV file. They were using this for some audit purpose. A developer needed to clone a git repository and then open the .sql files in Azure Data Studio. A DBA wanted to decide whether you choose a clustered or non-clustered index for a table that contains sales information and is often queried for a few rows based on a date. Why would one index be better than the other? Don't answer these questions, as this is homework for others. However, if you could share a list of things you would expect your new coworker to know or solve, that will help us raise the bar for what we expect from our colleagues. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [PostgreSQL Copy]( Shivayan Mukherjee from SQLServerCentral In this article you will learn how to use the COPY command in PostgreSQL to copy a database or a table. [External Article]( [Overcoming Organization Challenges in Cloud Migration: A Webinar for Senior IT Leaders]( Additional Articles from Redgate Is your team looking to modernize and migrate your data but have questions about the complex organizational challenges? This webinar panel on June 29th covers all aspects of data modernization and migration that senior IT leaders should be aware of, including organization culture, communication, and politics. Why not send it to your boss? [External Article]( [Data Transfer from Snowflake to Azure Blob using Synapse Analytics]( Additional Articles from MSSQLTips.com Two of the top cloud services right now are Azure Synapse and Snowflake. Whatever your organization is implementing or if you need to transfer data to/from those services, it is important to know how to interconnect them and be able to transfer data between services. [Blog Post]( From the SQL Server Central Blogs - [Working with ALS – ALS Awareness Month 2023]( DataOnWheels from DataOnWheels I have been living with ALS for the past two years. I was diagnosed in September 2022 but had my first symptoms and started researching what was going on... [Blog Post]( From the SQL Server Central Blogs - [Stacked Bar Charts vs. Line Graphs–Which is Better?]( Steve Jones - SSC Editor from The Voice of the DBA I ran across an interesting post from Rita Fainshtein that looked at the different types of graphs for a set of data. I thought that was interesting, so I... [Expert Performance Indexing in Azure SQL and SQL Server 2022]( [Expert Performance Indexing in Azure SQL and SQL Server 2022]( Site Owners from SQLServerCentral Take a deep dive into perhaps the single most important facet of query performance―indexes―and how to best use them. Newly updated for SQL Server 2022 and Azure SQL, this fourth edition includes new guidance and features related to columnstore indexes, improved and consolidated content on Query Store, deeper content around Intelligent Query Processing, and other […]   Question of the Day Today's question (by Steve Jones - SSC Editor):  Secure Enclave Attestation What does secure enclave attestation do in Always Encrypted? 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) Max Managed Instance CPUs In May 2023, what is the maximum number of vCores I have have on any type of Azure SQL Managed Instance? Answer: 80 vCores Explanation: The maximum number of cores is 80, in both General Purpose and Business Critical editions. The Memory Optimized edition can have 64 vCores. Ref: Overview of Azure SQL Managed Instance resource limits - [ [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 [Vulnerability Assessment removed?]( - In Brent Ozar's list of links today, I learned that Microsoft has removed the Vulnerability Assessment tool from SSMS as of v19.1. I recently started using it in my SQL Server environment and found it a great tool -- I was able to demonstrate to management why some of their security shortcuts were a bad […] SQL Server 2017 - Development [Need help with a Query logic]( - I have a table which tracks of activity when users from one specific app login and logout. But I have seen some cases where let's say a user logins at 11:40 on March 24th and then logins again at 11:55 on the same day without logging out. The application doesn't allow that but I am […] SQL Server 2016 - Administration [Database copy issue through - COPY DB WIZARD]( - I have a production server with SQL server 2016 version (13.0.5026.0) and a database with 25 GB of Data and 525 tables in it. Now, I want to transfer this production DB to my development environment with SQL server 2016 version (13.0.5026.0). I am using DB copy wizard for DB copy to the new instance […] SQL Server 2016 - Development and T-SQL [Help with a SQL Unpivot]( - I am trying to use unpivot to extract some data, however, the results are getting messed up on at least one record. By this I mean it is out of order which it should be CLNUM: MODEL: CLSTATUS: CLSTATDATE: CLSTOP: Here is the query any help or advice would be appreciated. WITH ClientsWithOpenMattersCTE (ClientNo, HowMany) […] [SQL help with partition]( - With the following data, I need to create a column that will display a counter for each VID, Acc where PrgFld is not 'XXX' I am trying to do this with partition, but it's increasing the counter when PrgFld is XXX. I would prefer to not increase the counter or show 0 for XXX. Is […] [NOT LIKE Alternatives in WHERE clause]( - Hello, I need help to improve an existing (hence I cannot change table's structure nor the content's logic) WHERE clause, please. The table consists of returned products with their respective reason(s). In case there is more than one reason, they are concatenated. The request as shown in the mock-up below, has to retrieve a list […] Administration - SQL Server 2014 [Database backups going to device]( - Hi Friends, How can I know the exact device where my backups are going on? I only see {67BAB2D0-A1DC-44F8-BF73-EBAFD5AE3220}16 as one device. However, I do not find out where are my backup files located. Some databases are going to a particular drive, but others are using that expression, Thank you, Best Regards, SQL Server 2019 - Administration [Upgrading from 2016 to 2019 with Encryption]( - Hi, I have a few AWS EC2 instances currently running SQL Server 2016. I'd now like to upgrade these to SQL Server 2019 but the problem is that some of the data has been encrypted at the column level. I understand that, after SQL 2016, the encryption algorithm changed from SHA1 to SHA2. Would this […] SQL Server 2019 - Development [how to de escalte a isolation level, and are there any implications.]( - hi, I have one nested transaction and want to deescalate to outer transaction's isolation/ or lower isolation. is it the right way? SET TRANSACTION ISOLATION LEVEL read committed; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to do some job in read commited SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRY BEGIN TRAN DBCC USEROPTIONS --want to […] [Advanced SQL query help]( - Hi, I am looking for some help with two queries using the following table/data. It would be great to see the most efficient way to achieve these. Queries 1: Show all homes with rent above the average rent value for their Company 2: Show the number of homes registered 0-3 months, 4-6 months, 6-12 months, […] [MSSQL Service Broker DSQL operation on another database causing Broker failure]( - I have established a Service Broker configuration based on Eitan Blumin's excellent example. Advanced Service Broker Sample: Asynchronous Triggers I have crafted my own Stored Procedure. I can get the example to work when I'm performing activities within the same database. If I try to operate outside the current DB I get errors. I'm new […] [Extracting multiple fields from one column]( -   Hello, I have a situation. I am trying to show each distinct event category, event action and event label from 'Hits' column and show the number of times each event occurred and the month that the event occurred– for labels containing “GNAV”. I am not sure why but something is wrong. Do you have […] Analysis Services [How can I show the last opening period for each project in my project dimension]( - I have two attributes hierarchies in my dimension [Dim Project]: Project Code Opening date Project A with 3 opening date : 2023-01-01 , 2023-01-02 and 2023-01-03 Project B with 2 opening date : 2023-01-02 , 2023-01-04 MDX query : Select [Dim Project].[Project Code] .[Project Code].Allmembers * [Dim Project].[Opening date].[Opening date].Allmembers on 0 [Measures].[Measure1] on 1 […] SQL Server 2022 - Administration [Allow only encrypted connections.]( - Hi In SQL22, Is there a way to allow only / force encrypted connections? I have 'force encryption' on and have cert installed:  But when I connect via SSMS I can unselect ' Encrypt Connection' and it connects. My understanding is that means that it is allowing non-encrypted connections: How do I make my […] SQL Server 2022 - Development [SQL Query]( - Hi, i have a sample table: table ID  flag  name 1    0   test1 1    0   test2 1    1   test3 2    0   test4 2    0   test5 3    1   test6 4  […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -