Laden...
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Inserting Sequences | |
I created a new sequence in SQL Server 2022 with this code. CREATE SEQUENCE myseqtest START WITH 1 INCREMENT BY 1; GO I want to use this to insert some data from another table into a new table with this sequence. Which of these queries shows the way to do this efficiently? -- 1 INSERT dbo.NewMonthSales (SaleID, saleyear, salemonth, currSales) SELECT NEXT VALUE FOR myseqtest , ms.saleyear , ms.salemonth , ms.currMonthSales FROM dbo.MonthSales AS ms; GO -- 2 INSERT dbo.NewMonthSales (SaleID, saleyear, salemonth, currSales) SELECT NEXT VALUE , ms.saleyear , ms.salemonth , ms.currMonthSales FROM dbo.MonthSales AS ms, myseqtest; GO --3 DECLARE mycurs CURSOR FOR SELECT ms.saleyear , ms.salemonth , ms.currMonthSales FROM dbo.MonthSales AS ms DECLARE @yr INT, @mn INT, @sales NUMERIC(10,2) FETCH NEXT FROM mycurs INTO @yr, @mn, @sales WHILE @@FETCH_STATUS = 0 BEGIN INSERT dbo.NewMonthSales (SaleID, saleyear, salemonth, currSales) SELECT NEXT VALUE FOR myseqtest , @yr , @mn , @sales FETCH NEXT FROM mycurs INTO @yr, @mn, @sales END | |
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) |
Partial Backups II What is a partial backup for a read-write database? Answer: a backup of the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files Explanation: A partial backup contains a backup of the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. Ref: Partial backups - https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server?view=sql-server-ver16 |
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 2016 - Administration |
KB5046856 fails to install - Hello experts, The following SQL update is failing to install on some of our SQL Servers with the following error. Is anyone else seeing this? I've tried Googling the error but have not found a specific fix for this issue. Thanks for any help. -- webrunner Update: Installation Failure: Windows failed to install the following […] |
Perfomance in views - Hello, I want to ask you about the following case that I live with a company in my country. They have a SQL server licensed with SQL enterprice ver 2016, where in the same instance about 25 databases coexist and in particular the main one and heart of all the systems, the Erp base. There […] |
Development - SQL Server 2014 |
Different number of records returned from SSMS and C# ExecuteReader - I am using the EXACT same query in SQL Server Management Studio (2014) as I am in my C# program, but I get different results. How is this possible? SELECT tblWTHistory.Date, tblWTHistory.EstCompDate, tblOperator.BadgeID, tblOperator.OperatorName, tblWTHistory.WONum, tblWTHistory.WOType, tblWTHistory.PartNum, tblWTHistory.CellReceivedQty, tblWTHistory.OpNum, tblWorkCenter.WorkCenterName, tblWorkType.WorkTypeName, tblWTHistory.WorkQty, tblWTStatus.StatusName, tblWTHistory.ReasonID, tblWTReasons.ReasonName, tblWTHistory.Rework, tblWTHistory.OpComment FROM tblWTHistory INNER JOIN tblWTReasons ON tblWTHistory.ReasonID = […] |
SQL Server 2019 - Administration |
Column Encryption/Exporting Windows Certificate - So random question about Windows Certificates -- I administer and develop a SQL Server DB App -- Back End is on an Azure Server/Front End is MS Access, about 35 End Users. We employ column encryption on a handful of fields, and re encrypt every 3 months or so. When this encryption is done by […] |
SQL Server 2019 - Development |
where to find information about xE sqlserver.databases_bulk_copy_throughput - where to find information about xE sqlserver.databases_bulk_copy_throughput ? I'm searching for de description of all attributes ( e.g. "count" - is it number of B/KB/MB/GB or number of rows ? ) |
bulk load fails to for SAS when using the ms ODBC Driver 18 for SQL Server - bulk load fails to for SAS when using the ms ODBC Driver 18 for SQL Server. Aparently SAS has this documented, and the solution is to revert to v11 SAS Problem Note 69064 Are there any alternatives besides downgrading to v11 ? |
Unique Count of IDs per 3 month period - I have an Appointments table with the fields MemberID and DateOfConsultation. Each member may have more than 1 consultation, with the same or different dates. I want to get a rolling 3 month period, where for the latest month, I get the data relevant to all the consultations that happened in that month and the […] |
Unable to delete records from table - Hi, Im unable to delete records from a table.Im a little surprised as have not witnessed this. The SQL server runs on MSSQL 2019 RTM. I use the below queries. select top (5) * from Journal where createddatetime<'2006-01-01' -- To figure out the records below year 2006 Fetched the 5 top queries Delete from Journal […] |
SSDT |
SSIS Foreach From Variable Enumerator Multiple Variables - Currently I have a working script task which reads a directory to find filename(s) that match a certain regex. The ArrayList object is passed to a Foreach Loop which uses a Foreach From Variable Enumerator and a single variable index = 0 to BULK INSERT into a table. I am trying to add additional functionality […] |
SQL Server 2022 - Administration |
DBCC Clone Database Failing on SQL 2022 - When running clone sql is changing one specific table to History table and its failing. Please suggest if there is any bug |
Upgrading from SQL Server 2016 to 2022 - When we upgraded from SQL Server 2008 R2 to 2016 (Enterprise Edition) many years back, research found that it was advisable for us to run a number of steps due to changes in the cardinality estimation process, and to cater for any other changes due to databases being upgraded to the latest compatibility level: Run […] |
Backup taking too much time - Hi Experts, One of my Large database having about 4TB in size is taking almost 3 days to complete the backup . To reduce the backup I stripped the same to 4 different cluster drives and still its taking the same time. The data stored is all documents and it uses filestream. Below are the […] |
Keeping QUERIES for Using Again and Adding DB to Git/Github and VS Code - While I know that it will speed my learning to type queries as I need them, sometimes I have put other people's queries to good use, queries that I lack the skills to create but will likely use in the future. Alas, I find no way to store and retrieve queries making them very accessable. No […] |
Found a Tool Very Helpful for Beginner - I struggled with remembering the conventions I'd chosen when creating column names and data types. Likewise I struggle - continually - with relationships and keeping track of Foreign Key assignments. While I have not fully resolved the last issue, I stumbled upon a query that will list all the data elements in a database along […] |
SQL Server 2022 - Development |
After an Oracle migration, write out a function into a View SELECT statement - This is the function oracle.xxxfloat BEGIN IF @first IS NULL OR @second IS NULL RETURN NULL IF @first < @second RETURN @first RETURN @second END Here is the select statement getting column2, where I need to write the function 'oracle.xxxfloat' inline as part of the select so I can phaze out the above function. In […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Laden...
Laden...
© 2025