Common SQL Server Samples

1. Remove the space in the record

LTrim () Remove left space RTrim () Remove right space

Select LTrim (RTrim (field name)) from table name.

2, increase

INSERT into table_name (column 1, column 2,...) Values (value 1, value 2,....) INSERT into table name values (value 1, value 2,....) Insert into table name 1 (column name 1, column Name 2 ...) select column name A, column name B ... from table Name 2 (provided that table 1 already exists and the field data type is compatible with table 2) For example: INSERT INTO RPC (DATE,BM,KB,TRGS,CPGS,DCL , rpb001,rpb002) SELECT * from RPB

3. By deleting

DELETE from table name WHERE column name = value

4, change

update  table name  SET  column name  =  new value  WHERE  column name  =  a value of 5, new trigger 6, transaction begin  tranupdate copma set modi_date =  ' 20140609000000000 '  WHERE MA001=  ' 001 ' Select * from copma select * from copma where ma001= ' 001 ' rollback tran --ROLLBACK TRANSACTION commit tran --complete and SAVE transaction 7, stored procedure 8, cursor 9, deduplication duplicate record "There are two meanings of duplicate records, one is a completely duplicate records, That is, all the fields are duplicated records, and the second is some key field duplicate records, such as the Name field is repeated, and the other fields are not necessarily repeated or can be ignored. For the first repetition, it is easier to solve, using select distinct * from tablename second repetition (subsequent supplement) 10, viewing triggers or stored procedures EXEC SP_ helptext  trigger name/stored Procedure name 11, determine if a table already exists in the database, or delete the table if (Exists (select * from sysobjects  Where xtype= ' U '  and name= ' table name ') drop table  table name  12, detect # #CFGEJGS是否存在, delete the table  if  exists  (select * from tempdb.dbo.sysobjects where id = object_id (N ' tempdb: # #CFGEJGS ')  and type= ' U ')  drop table # #CFGEJGS13, will365 days a year Insert the date field in the Dclhz table to note the use of the DateAdd function select top 365 id=identity ( int,0,1)  into  tmp from syscolumns a,syscolumns b insert into dclhz (DATE)   Select dateadd (Day,id, ' 2014-01-01 ') from tmp drop table tmp14, view the amount of space occupied by a table sp_spaceused ' Name of table '

Common SQL Server Samples