Classic SQL statement

Spanstylefont-size: 14px; 1, basic 1, Note: Create a database CREATEDATABASEdatabase-name2, Note: delete the database dropdatabasedbname3, note: Backup sqlserver --- create backup data snapshot, testBack, c: mssql7backupMyNwi

Span style = font-size: 14px; 1. Basic 1. Description: create database database-name 2. Description: delete DATABASE drop database dbname3. Description: backup SQL server --- create deviceUSE masterEXEC sp_addumpdevice disk, testBack, c: \ mssql7backup \ MyNwi for backup data

1. Basic 1. Description: create database database-name 2. Description: delete database drop DATABASE dbname3. Description: back up SQL server --- create the deviceUSE masterEXEC sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ MyNwind_1.dat 'of the BACKUP Data --- start TO back up the BACKUP DATABASE pubs TO testBack 4. Instructions: create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...) create A new table based on an existing table: A: create table tab_new like tab_old (create A new table using the old table) B: create table tab _ New as select col1, col2... From tab_old definition only5. Description: Delete the new table drop table tabname 6. Description: add an Alter table tabname add column col type. Note: The Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type. 7. Description: add a primary key: Alter table tabname add primary key (col) Description: delete a primary key: Alter table tabname drop primary key (col) 8. Description: Create an index: create [unique] index idxname on tabname (col ....) Delete index: drop index idxname Note: The index cannot be changed. To change the index, you must delete it and recreate it. 9. Description: create view viewname as select statement Delete view: drop view viewname10. Description: select a few simple basic SQL statements: select * from table1 where range insert: insert into table1 (field1, field2) values (value1, value2) delete: delete from table1 where range update table1 set field1 = value1 where range search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information! Sort: select * from table1 order by field1, field2 [desc] Total: select count as totalcount from table1 sum: select sum (field1) as sumvalue from table1 average: select avg (field1) as avgvalue from table1 max: select max (field1) as maxvalue from table1 min: select min (field1) as minvalue from table111 Note: several advanced query computation terms: the UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or table2. B: The distinct t operator. The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated. C: INTERSECT operator the INTERSECT operator derives a result table by only including rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated. Note: The query results of several computation words must be consistent. 12. Note: Use outer join A and left (outer) join: left outer join (left join): the result set contains matching rows of the connected table, and all rows of the left join table. SQL: select. a,. b,. c, B. c, B. d, B. f from a left out join B ON. a = B. cB: right (outer) join: right outer join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table. C: full/cross (outer) join: full outer join: includes not only matching rows in the symbolic join table, but also all records in the two connection tables. 12. Group: Group by: A table. After grouping is completed, you can only obtain Group-related information. Group-related information: (Statistical Information) criteria for grouping count, sum, max, min, and avg) in SQLServer: text, ntext, fields of the image type can be grouped based on fields in the selecte statistics function. They cannot be put together with common fields. 13. perform operations on the database: detaching the database: sp_detach_db; and attach the database: sp_attach_db indicates that the complete path 14 is required for appending. how to modify the Database name: sp_renamedb 'old _ name', 'new _ name' 2. Upgrade 1. Description: copy the table (only copy the structure, source table name: a new table name: b) (Access available) Method 1: select * into B from a where 1 <> 1 (for SQlServer only) Method 2: select top 0 * into B from a2, note: copy table (copy data, source table name: a target table name: B) (Access available) ins Ert into B (a, B, c) select d, e, f from B; 3. Description: Table copy across databases (absolute path for specific data) (Access available) insert into B (a, B, c) select d, e, f from B in 'specific database' where condition example :.. from B in '"& Server. mapPath (". ") &" \ data. mdb "&" 'where .. 4. Description: subquery (table name 1: a table name 2: B) select a, B, c from a where a IN (select d from B) or: select, b, c from a where a IN (, 3) 5. Description: select. title,. username, B. adddate from table a, (select max (addda Te) adddate from table where table. title =. title) b6, Description: External join query (table name 1: a table name 2: B) select. a,. b,. c, B. c, B. d, B. f from a left out join B ON. a = B. c7, Description: Online View query (table name 1: a) select * from (SELECT a, B, c FROM a) T where t. a> 1; 8. Description: between usage. When between restricts the Data Query range, it includes the boundary value. not between does not include select * from table1 where time between time1 and time2select, b, c, from table1 where a not between value 1 and value 29. Description: select * From table1 where a [not] in ('value 1', 'value 2', 'value 4', 'value 6') 10. Description: two joined tables, delete information that has not been found in the secondary table in the primary table: delete from table1 where not exists (select * from table2 where table1.field1 = table2.field1) 11. Description: Table 4 Joint query problem: select * from a left inner join B on. a = B. B right inner join c on. a = c. c inner join d on. a = d. d where ..... 12. Note: Five minutes ahead of schedule reminder SQL: select * from Schedule where datediff ('minute ', f Start Time, getdate ()> 513. Description: number of SQL statements Database paging select top 10 B. * from (select top 20 primary key field, sorting field from table name order by sorting field desc) a, table name B where B. primary key field =. primary key field order by. specific implementation of sorting fields: Database paging: declare @ start int, @ end int @ SQL nvarchar (600) set @ SQL = 'select top '+ str (@ end-@ start + 1) +' + from T where rid not in (select top '+ str (@ str-1) + 'rid from T where Rid>-1) 'exec sp_executesql @ SQL Note: a variable cannot be directly followed after top, so this is the only special method in actual application. Rid is an identifier column. If there are specific fields after top, this is very beneficial. This avoids the inconsistency in the actual table after the query results of the top field if it is a logical index. (the data in the logical index may be inconsistent with that in the data table, if the query is indexed, the index is first queried.) 14. Note: select top 10 * form table1 where range of the first 10 records 15. Description: select all the information of the largest a record corresponding to the data with the same B value in each group (usage similar to this can be used for monthly ranking of the forum and Analysis of popular products each month, rank by subject score, etc .) select a, B, c from tablename ta where a = (select max (a) from tablename tb where tb. B = ta. b) 16. Description: includes all rows in TableA but not in TableB and TableC and removes all repeated rows to derive a result table (select a from tableA) except T (select a from tableB) Random T (select a from tableC) 17. Description: select top 10 * from tablename order by newid () 18. Description: select newid () 19. Note: delete record 1), delete from tablename where id not in (select max (id) from tablename group by col1, col2 ,...) 2), select distinct * into temp from tablename delete from tablename insert into tablename select * from temp comment: this operation involves moving a large amount of data, this method is not suitable for large capacity but Data Operations 3). For example, if you import data to an external table, for some reasons, only a part of the data is imported for the first time. But it is difficult to determine the specific location, so that only the next full import will generate a lot of repeated fields, how to delete duplicate fields alter table tablename -- add an auto-incrementing column add column_ B int identity () delete from tablename where column_ B not in (select max (column_ B) from tablename group by column1, column2 ,...) alter table tablename drop column column_b20: list all the table names in the database. select name from sysobjects where type = 'U' // U indicates user 21. Description: list all columns in the Table. select name from syscolumns where id = object_id ('tablename') 22. Description: lists the fields of type, vender, and pcs, which are arranged by the type field. case can be easily selected, similar to case in select. Select type, sum (case vender when 'a then pcs else 0 end), sum (case vender when 'C' then pcs else 0 end ), sum (case vender when 'B' then pcs else 0 end) FROM tablename group by type: type vender pcs computer A 1 computer A 1 Disc B 2 Disc A 2 mobile phone B 3 mobile phone C 323, Description: initialization TABLE table1TRUNCATE TABLE table124, description: select top 5 * from (select top 15 * from table order by id asc) records from 10 to 15) table _ alias order by id desc 3. Tips 1, 1 = 2, in the SQL statement When a combination is used, "where 1 = 1" indicates that all "where 1 = 2" is selected, not all, for example: if @ strWhere! = ''Ininset @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] where' + @ strWhere endelse beginset @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] 'end we can directly write an error! Directory item not found. Set @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] where 1 = 1 stationary '+ @ strWhere 2. shrink the database -- re-create the index dbcc reindexdbcc indexdefrag -- contract data and log dbcc shrinkdbdbcc SHRINKFILE3 and compress database dbcc shrinkdatabase (dbname) 4. Transfer the database to a new user with the existing user permission exec sp_change_users_login 'Update _ one', 'newname', 'oldname' go5. Check the backup set restore verifyonly from disk = 'e: \ dvbbs. bak '6. Fix database alter database [dvbbs] SET SINGLE_USERGODBCC CHECKDB ('dvbbs ', r Epair_allow_data_loss) with tablockgoalter database [dvbbs] SET MULTI_USERGO7, log clearing set nocount ondeclare @ LogicalFileName sysname, @ MaxMinutes INT, @ NewSize INTUSE tablename -- Name of the database to be operated SELECT @ LogicalFileName = 'tablename _ log', -- log file name @ MaxMinutes = 10, -- Limit on time allowed to wrap log. @ NewSize = 1 -- the size of the log file you want to set (M) Setup/initializeDECLARE @ OriginalSize intSELECT @ OriginalSize = size FROM sy Sfiles WHERE name = @ LogicalFileNameSELECT 'ininal Size of '+ db_name () + 'Log is' + CONVERT (VARCHAR (30), @ OriginalSize) + '8 K pages or '+ CONVERT (VARCHAR (30), (@ OriginalSize * 8/1024 )) + 'mb' FROM sysfiles WHERE name = @ LogicalFileNameCREATE TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @ Counter INT, @ StartTime DATETIME, @ TruncLog VARCHAR (255) SELECT @ StartTime = GETDATE (), @ TruncL Og = 'backup log' + db_name () + 'WITH TRUNCATE_ONLY 'dbcc SHRINKFILE (@ LogicalFileName, @ NewSize) EXEC (@ TruncLog) -- Wrap the LOG if necessary. WHILE @ MaxMinutes> DATEDIFF (mi, @ StartTime, GETDATE () -- time has not expired AND @ OriginalSize = (SELECT size FROM sysfiles WHERE name = @ LogicalFileName) AND (@ OriginalSize * 8/1024)> @ NewSize BEGIN -- Outer loop. SELECT @ Counter = 0 WHILE (@ Counter <@ OriginalSize/16) AND (@ Counter <50000) BEGIN -- update INSERT DummyTrans VALUES ('fill log ') DELETE DummyTrans SELECT @ Counter = @ Counter + 1 end exec (@ TruncLog) ENDSELECT 'final Size of '+ db_name () + 'Log is' + CONVERT (VARCHAR (30 ), size) + '8 K pages or '+ CONVERT (VARCHAR (30), (size * 8/1024 )) + 'mb' FROM sysfiles WHERE name = @ LogicalFileNameDROP TABLE DummyTransSET NOCOUNT OFF8. Note: CHANGE Table exec sp_changeobjectowner 'tablename', 'dbo' 9. Store and change all tables create procedure dbo. reply @ OldOwner as NVARCHAR (128), @ NewOwner as NVARCHAR (128) ASDECLARE @ Name as NVARCHAR (128) DECLARE @ Owner as NVARCHAR (128) DECLARE @ OwnerName as NVARCHAR (128) DECLARE curObject cursor for select 'name' = Name, 'owner' = user_name (uid) from sysobjectswhere user_name (uid) = @ OldOwnerorder by nameOPEN curObjectF Etch next from curObject INTO @ Name, @ OwnerWHILE (@ FETCH_STATUS = 0) BEGIN if @ Owner = @ OldOwner begin set @ OwnerName = @ OldOwner + '. '+ rtrim (@ Name) exec sp_changeobjectowner @ OwnerName, @ NewOwnerend -- select @ name, @ NewOwner, @ OldOwnerFETCH next from curObject INTO @ Name, @ OwnerENDclose curObjectdeallocate curObjectGO10. SQL server directly writes data cyclically. declare @ I intset @ I = 1 while @ I <30 begin insert into test (userid) Values (@ I) set @ I = @ I + 1end case: as shown in the following table, all the failures that have not passed are required, on the base score for each increase of 0.1, let them pass: Name score Zhangshan 80 Lishi 59 Wangwu 50 Songquan 69 while (select min (score) from tb_table) <60) beginupdate tb_table set score = score * 1.01 where score <60if (select min (score) from tb_table)> 60 break else continueend data development-classic 1. sort By surname strokes: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as // From less to more 2. database encryption: se Lect encrypt ('original password') select pwdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, different encrypt ('original password') select pwdencrypt ('original password') select pwdcompare ('original password', 'encrypted password') = 1 -- same; otherwise, they are different. 3. retrieve the table fields: declare @ list varchar (1000), @ SQL nvarchar (1000) select @ list = @ list + ',' + B. name from sysobjects a, syscolumns B where. id = B. id and. name = 'table a' set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table a' exec (@ SQL) 4. view Hard Disk Partition: EXEC master .. xp_fixeddrives5. compare tables A and B to see if they are equal: if (select checksum_agg (binary_checksum (*) from A) = (select checksum_agg (binary_checksum (*) from B) print 'equal to 'elasticprint' not equal to '6. kill all event Inspector processes: DECLARE hcforeach cursor global for select 'Kill '+ RTRIM (spid) FROM master. dbo. sysprocessesWHERE program_name IN ('SQL filer', n' SQL event profiler') EXEC sp_msforeach_worker '? '7. record Search: Select Top N * From Table --------------------------------------- N to M records (primary index ID required) select Top M-N * From table Where ID in (Select Top m id From Table) Order by ID Desc ------------------------------------ N to the end record Select Top N * From Table Order by ID Desc Case Example 1: A table has more than 10 thousand records. The first RecID field of the table is an auto-increment field. Write an SQL statement to find the 31st to 40th records of the table. Select top 10 recid from A where recid not in (select top 30 recid from A) Analysis: if this is the case, some problems may occur if the recid has A logical index in the table. Select top 10 recid from A where ...... Select top 30 recid from A is searched from the index, while select top 30 recid from A is searched in the data table. As A result, the order in the index may be different from that in the data table, in this way, the queried data is not originally expected. Solution 1: Use order by select top 30 recid from A order by ricid. If this field is not auto-incrementing, problem 2 will occur. In that subquery, add the following conditions: select top 30 recid from A where recid>-1 Example 2: query the last record in the table, and do not know how much data the table has and the table structure. Set @ s = 'select top 1 * from T where pid not in (select top '+ str (@ count-1) + 'pid from T) 'print @ s exec sp_executesql @ s9: Obtain the select Name from sysobjects where xtype = 'U' and status> = 010: obtain all the fields of a table. select name from syscolumns where id = object_id ('table name ') select name from syscolumns where id in (select id from sysobjects where type = 'U' and name = 'table name') two methods have the same effect 11: view views, stored procedures, and functions related to a table T. * from sysobjects a, syscomments B where. id = B. id and B. text like '% table name %' 12: view all stored procedures in the current database select name as stored procedure name from sysobjects where xtype = 'P' 13: query all databases created by the user, select * from master .. sysdatabases D where sid not in (select sid from master .. syslogins where name = 'sa ') or select dbid, name AS DB_NAME from master .. sysdatabases where sid <> 0x0114: queries the fields and Data Types of a table. select column_name, data_type from informa Tion_schema.columnswhere table_name = 'table name' 15: Data operations between databases on different servers -- create a connection to the server exec sp_add+server 'itsv', '', 'sqloledb ', 'remote server name or IP address 'exec sp_addjavassrvlogin 'itsv', 'false', null, 'username ', 'Password' -- Query example select * from ITSV. database Name. dbo. table name -- import example select * into table from ITSV. database Name. dbo. table name -- delete the linked server exec sp_dropserver 'itsv' and 'droplogins' later -- connect to the remote/LAN data (openrowset/openquery/opendatasource) -- 1. openrowset -- Query Query example select * from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. table Name) -- generate a local table select * into table from openrowset ('sqlodb', 'SQL Server name', 'username', 'Password', database name. dbo. table Name) -- import the local table into the remote table insert openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. table Name) select * from local table -- update local table update bset B. column A =. column A from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. table Name) as a inner join local table bon. column1 = B. column1 -- create a connection for openquery usage -- first create a connection to create the connection server exec sp_add+server 'itsv', '', 'sqloledb ', 'remote server name or IP address' -- Query select * FROM openquery (ITSV, 'select * FROM database. dbo. table name ') -- import the local table to the remote table insert openquery (ITSV, 'select * FROM database. dbo. table name ') select * from local table -- update local table update bset B. column B =. column BFROM openquery (ITSV, 'select * FROM database. dbo. table name ') as a inner join local table B on. column A = B. column A -- 3, opendatasource/openrowsetSELECT * FROM opendatasource ('sqloledb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). test. dbo. roy_ta -- import the local table to the remote table insert opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). database. dbo. table Name select * from local table SQL Server basic functions 1. string function length and analysis use 1, datalength (Char_expr) to return the number of characters contained in the string, but does not contain the following space 2, substring (expression, start, length) to obtain the substring, the subscript of the string is from "1", start is the starting position, length is the string length, the actual In the application, len (expression) is used to obtain its length 3. right (char_expr, int_expr) returns the int_expr character on the right side of the string, and returns the opposite 4 with left, isnull (check_expression, replacement_value) if check_expression is null, the value of replacement_value is returned. If it is not null, The check_expression character operation class 5 is returned. The Sp_addtype custom numeric type is EXEC sp_addtype birthday, datetime, 'null' 6, set nocount {on | off} does not include information about the number of rows affected by the Transact-SQL statement. If some statements contained in the stored procedure do not return much actual data, this setting greatly reduces network traffic and significantly improves performance. Set nocount is SET during execution or runtime, rather than during analysis. When set nocount is ON, no count is returned (indicating the number of rows affected by the Transact-SQL statement ). When set nocount is OFF, return count common sense in the SQL query: the maximum number of tables or views that can be followed after from: 256 Order by appears in the SQL statement. When querying, sort first, in SQL, the maximum capacity of a field is 8000. For nvarchar (4000), nvarchar is a Unicode code. SQLServer2000 synchronous replication technology implementation step 1. Preparation 1. on the Publishing Server, the subscription server creates a windows user with the same name and sets the same password, as an effective access user for publishing snapshot folders-management tools-Computer Management-users and groups-right-click users-create a user (SynUser) that belongs to the administrator group to log on to windows 2. on the Publishing Server, create a new shared directory to store the released snapshot files. For details, click "My Computer -- D: \" to create a directory named: PUB -- Right-click the newly created directory -- properties -- share -- select "share this folder" -- use the "permission" button to set specific user permissions, make sure that the user (SynUser) created in step 1 has all permissions on the folder-OK 3. set the startup user (publish/subscribe server) of the SQL Server Agent service to start -- program -- management tool -- service -- Right-click SQLSERVERAGENT -- property -- Login -- select "this account" -- enter or select the windows login user name (SynUser) created in step 1 -- "password" and enter the password of this user. 4. set the SQL Server Authentication Mode to solve the permission issue during connection (this setting is done by the publishing/subscription servers) enterprise Manager -- Right-click SQL instance -- properties -- Security -- authentication -- select "SQL Server and Windows" -- OK 5. register the enterprise manager on the Publishing Server and subscription Server -- Right-click the SQL Server group -- create an SQL Server registration... -- next -- available server, enter the name of the remote server you want to register -- add -- next -- connection usage, select the second "SQL Server Authentication" -- next -- enter the user name and password (SynUser) -- next -- select the SQL Server group, or create a new group -- next -- finish 6. if you can only use IP addresses and cannot use computer names, register the server alias for them (this step is not used in implementation) (configure at the connection end) For example, if the server name is configured on the subscription server, the IP address of the Publishing Server is entered) start -- program -- Microsoft SQL Server -- client network utility -- alias -- add -- network library select "TCP/IP" -- Server alias enter SQL Server name -- connection parameter -- enter Server name SQL Server IP Address -- If you modify the SQL port, unselect "dynamically determine port", and enter the corresponding port number 2. Formal configuration 1. Configure the Publishing Server to open the enterprise manager on the Publishing Server (B, C, D) perform the following steps: (1) select [configuration Publishing, subscription server and Distribution] from the [copy] sub-menu in the [tools] drop-down menu to display the configuration publishing and distribution wizard (2) [Next] You can select the distribution server as the distribution server or another SQL Server (select your own) (3) [Next] set the snapshot folder to use the Default \ servername \ Pub (4) [next] custom configuration options: Yes, let me set the distribution database attribute to enable Use the following default settings (recommended) (5) [next] to set the name and location of the distribution database. Use the default value (6) [Next] enable the Publishing Server select as the Publishing Server (7) [next] select the database and publishing type to be published (8) [next] Select register subscription server (9) [Next] complete configuration 2. Create a publication publishing server B, C, and D (1) from the [copy] sub-menu in the [tools] menu, select [create and manage Publishing] command (2) Select the database for which you want to create a publication, and then click [Create Publishing] (3) in the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed. The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can go to help) (4) Click [next] system requirements to specify the database server type that can subscribe to the release, SQLSERVER allows data replication between different databases, such as orACLE or ACCESS. But here we choose to run "SQL SERVER 2000" Database SERVER (5) Click [next] The system will pop up a dialog box that defines the article, that is, select the table to be published. Note: if you have selected transaction Publishing, you can only select a table with a primary key (6) and select the publishing Name and description (7). Select the custom publishing attribute Wizard: yes. I want to filter custom data, enable Anonymous subscription, or Disable other custom attributes to create a release according to the specified method (we recommend that you use a custom method) (8) [Next] select the method for filtering and publishing (9) [next] You can select whether to allow anonymous subscription. 1) if you select "signed Subscription", you need to add the subscription server method on the Publishing Server: [tools]-> [copy]-> [configure attributes of the publishing, subscription server, and Distribution]-> [subscription server] to add or else the subscription server requests subscription tip: changing publishing does not allow anonymous subscription. If anonymous subscription is still required, use the following solution: [Enterprise Manager]-> [copy]-> [publish content]-> [attribute]-> [subscribe option] Select allow anonymous request subscription 2) if you select If you select Anonymous subscription, the above prompt will not appear When configuring the subscription server (10) [next] configuring snapshot proxy Scheduling (11) [Next] complete the configuration. After the publication is created, the database for creating the publication becomes a shared database with the database name srv1 .. author has fields: id, name, phone, srv2. library name .. author has the following fields: id, name, telphone, and adress: srv1. library name .. the database name srv1 .. the srv1. library name is added to the author record .. if the phone field of author is updated, the srv1. library name is displayed .. telphone update for the author field -- */-- General processing steps -- 1. create a connection server on srv1 to operate srv2 in srv1, and synchronize exec sp_add1_server 'srv2', '', 'sqlodb', 'srv2 SQL Instance name or ip' exec sp_a Dd1_srvlogin 'srv2', 'false', null, 'username', 'Password' go -- 2. in the srv1 and srv2 brains, start msdtc (Distributed Transaction Processing Service) and set it to Automatic startup. My computer -- control panel -- Administrative Tools -- service -- Right-click Distributed Transaction Coordinator -- Property -- start -- and set the Startup Type to auto start go -- then create a job to call the above synchronization at regular intervals after processing the stored procedure, you can choose "Enterprise Manager"> "manage"> "SQL Server proxy"> "right-click a job"> "Create a job"> "general"> enter the job name ">" Step name ". "Enter the step name --" type "and select" Transact-SQL script (TSQL) "--" Database "select the database for command execution --" command "and enter the statement to be executed: exec p_process -- OK -- "scheduling" item -- create scheduling -- enter the scheduling name in "name" -- select your job execution schedule in "scheduling type" -- if you select "recurrence "-- click "change" to set your schedule and then start the SQL Agent service, and set it to automatic start. Otherwise, your job will not be executed as follows: my computer -- control panel -- Administrative Tools -- service -- Right-click SQLSERVERAGENT -- properties -- start type -- select "auto start" -- OK. -- 3. method 2 for synchronization: timed synchronization-create the following synchronization and processing stored procedure create proc p_process as in srv1-update modified data update B set name = I. name, telphone = I. telphone from srv2. library name. dbo. author B, author I where B. id = I. id and (B. name <> I. name or B. telphone <> I. telphone) -- insert the new data insert srv2. library name. dbo. author (id, name, telphone) select id, name, telphone from author I where not exists (select * from srv2. library name. dbo. author where id = I. id) -- delete the deleted data (if needed) delete B from srv2. database name. dbo. author B where not exists (select * from author where id = B. id) go