SQL scripts for full and differential backup databases

In the work, SQL Job needs to be created to back up the database regularly. Now, the script is recorded as follows. 1. Full backup: -- FULL declare @filename varchar(1024), @file_dev varchar(300) declare @path varchar(1024) set @path = N'F:\Backup\Plan2\'; declare @extension_name varchar(16) set @extension_name = ...

Posted on Wed, 27 May 2020 09:00:31 -0700 by benwhitmore

When a new data is inserted into the local database (sql server), it is synchronized to the server database

One of my classmates asked me how to synchronize the local database to the server when inserting new data. At that time, I first thought about the program logic control and corresponding processing. But sometimes our program is not easy to deal with. Can we start from the database? Isn't there a trigger in the database? It should be OK. Let's d ...

Posted on Sat, 04 Apr 2020 04:39:41 -0700 by Katanius

SQL server queries the tables and fields of a specific value in the whole database

In recent days, business departments need to use a report in the old SAP B1 system. However, since the report does not add time conditions, once the query is started, the B1 system will exit abnormally. Because the SQL corresponding to the report exists in the database, you want to find the SQL of the report, and then add time conditions to the ...

Posted on Thu, 02 Apr 2020 20:35:23 -0700 by drfate

How to generate and use CRUD stored procedures in SQL Server

Most database systems are based on four simplest data operations, which are called CRUD. The acronym stands for create, read, update, and delete. When developing and managing databases, CRUD stored procedures can be used to perform all data related tasks. The advantage of this stored procedure is that once it is written once, it can be reused ...

Posted on Wed, 11 Dec 2019 05:29:55 -0800 by bobthebullet990

SQLsever stored procedure paging query

Use stored procedures to implement paging query. The SQL statement is as follows: USE [DatebaseName] --Database name GO /****** Object: StoredProcedure [dbo].[Pagination] Script Date: 03/30/2019 10:36:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[Pagination] ( @SqlTable varchar(1000),--Table or vi ...

Posted on Sun, 08 Dec 2019 18:14:33 -0800 by delphi

SQL Server Query (sub query, full connection, equivalent connection, natural connection, left and right connection, intersection, union, difference set)

--Departmental table create table dept( deptno int primary key,--Department number dname nvarchar(30),--Department name loc nvarchar(30)--address ); --Employee list create table emp( empno int primary key,--Employee number ename nvarchar(30),--Employee name job nvarchar(30),--Employee work mrg int,--Employe ...

Posted on Fri, 06 Dec 2019 07:52:18 -0800 by kingsol

Generation and processing of SQL index fragment.

Reference in this article https://www.cnblogs.com/CareySon/archive/2011/12/22/2297568.html https://www.jb51.net/softjc/126055.html https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=sql-server-ver15 This article needs to“ Indexes ”And“ Stor ...

Posted on Thu, 28 Nov 2019 09:27:54 -0800 by gsv2com

UNPIVOT reverse perspective and dynamic reverse perspective stored procedure

I practiced PIVOT perspective the other day and realized the saving process of dynamic perspective< Dynamic PivotTable>https://www.cnblogs.com/insus/p/10888277.html Today, I will practice the function of MS SQL Server reverse perspective. First, prepare some data that can be inverted perspective:   IF OBJECT_ID('tempdb.dbo.#Part_ ...

Posted on Thu, 07 Nov 2019 08:13:37 -0800 by Petrushka

Reverse engineering of springboot + mybats has the process of generating domain from database

Environment: jdk1.8 (for springboot2.X and above) Maven (above 3.3. X)           spring boot 2.1.6           Idea 2019.1\              Fill it in here      Select the corresponding Jar, as shown below   The following is the most important operation. In pom.xml, import the jar required by mybats reverse engineering.   Co ...

Posted on Sun, 03 Nov 2019 01:45:46 -0700 by stone.cold.steve.austin

Timely Email Warning and Reminder for Blockage in Database (Part 2)

stay Timely Email Warning and Reminding of Blocking in Database (Part I) We build a new table to store blocked records, collect blocked stored procedures and configure sending mail to test ok, etc. Step 4: Create a storage process for sending mail. There are two modes that you can choose according to your needs Create a stored procedure for sen ...

Posted on Wed, 03 Jul 2019 15:53:38 -0700 by ksb24930