[SQL] special posts reveal SQL statements and paste your exquisite SQL statements

Look at the original stickers in detail: http://search.csdn.net/Expert/topic/1546/1546652.xml? Temp =. 8588983.

Note: copy a table (only copy structure, source table name: a new table name: B)
SQL: select * into B from a where 1 <> 1
-----------------------------------------------------------
Description: copy a table (copy data, source table name: a target table name: B)
SQL: insert into B (a, B, c) select d, e, f from B
-----------------------------------------------------------
Description: merged data (table name 1: Table a name 2: B)
SQL: select a, B, c from a union select d, e, f from B
-----------------------------------------------------------
Note: subquery (table name 1: Table a name 2: B)
SQL: select a, B, c from a where a IN (select d from B)
-----------------------------------------------------------
Description: displays the article, Submitter, and last reply time.
SQL: select a. title, a. username, B. adddate from table a, (select max (adddate) adddate from table where table. title = a. title) B
-----------------------------------------------------------
NOTE: Refer to table B to modify the inventory of Table A one by one. Table B has A sum, and table A is also associated with table B, meeting the conditions of Table.
SQL: UPDATE pharmacy inventory SET inventory quantity = inventory quantity-B. Usage FROM [pharmacy inventory],
(Select sum (usage) as usage, drug price code FROM prescription item
WHERE prescription No. = @ RecipeNo AND prescription No. = @ RecipeXNo
Group by drug price code
) B where a. Drug Price code = B. Drug Price code AND A. Warehouse name = @ DepotName
-----------------------------------------------------------
Table 1: Class/teacher Table 2 student/class Table 3 mathematics/student Table 4 Chinese/student
The query result is probably as follows:

Class 1 instructor 1
Student 1 mathematical language
Student 2: mathematical language
Class 2 instructor 1
Student 1 mathematical language
Student 2: mathematical language

Fail:
Student 1 class 1 teacher 1 mathematics and Chinese
Student 1 class 1 teacher 1 mathematics and Chinese
There is also an interface for entering students and scores

I used the inner join on statement between multiple tables, and it was very nice. After an afternoon, I wanted to smoke. At that time :)
View my questions about the four-table join query:
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 .....
-----------------------------------------------------------
Note: synchronize a data table on two SQL servers!

Drop procedure dbSync
GO

/* Data Synchronization */
Create procedure dbSync
@ STabelName varchar (255), -- Name of the table to be synchronized
@ SKeyField varchar (255), -- Keyword segment
@ SServer varchar (255), -- server name or IP address
@ SUserName varchar (255), -- the username used to log on to the server, generally sa
@ SPassWord varchar (32) -- password used to log on to the server
AS

/* Delete a temporary table */
If exists (select * from dbo. sysobjects where id = object_id (N 'temptbl') and OBJECTPROPERTY (id, N 'isusertable') = 1)
Drop table tempTbl


Declare @ SQL VARCHAR (2000)

/* Copy the data in the table @ sTabelName [remote] to the temporary table */

Set @ SQL = 'select * into tempTbl from'
Set @ SQL = @ SQL + 'opendatasource ('
Set @ SQL = @ SQL + ''' SQLOLEDB. 1 '','
Set @ SQL = @ SQL + ''' Persist Security Info = True; User ID = '+ @ sUserName
Set @ SQL = @ SQL + '; Password =' + @ sPassWord
Set @ SQL = @ SQL + '; Initial Catalog = toys; Data Source =' + @ sServer
Set @ SQL = @ SQL + '''). toys. dbo. '+ @ sTabelName

EXEC (@ SQL)

/* Insert the data not in the @ sTabelName [remote] Table in @ sTabelName [local] to the temporary table */

Set @ SQL = 'insert into tempTbl select * from '+ @ sTabelName + 'where [' + @ sKeyField + '] not in (select [' + @ sKeyField + '] from tempTbl)'

EXEC (@ SQL)

/* Clear the table @ sTabelName [local] */
Set @ SQL = 'truncate table' + @ sTabelName

EXEC (@ SQL)

-- Retrieve the column name
Declare @ MySql VARCHAR (2000)
Set @ MySql =''
Declare @ title varchar (20)

DECLARE titles_cursor CURSOR

SELECT name from syscolumns where id = object_id (@ sTabelName)

OPEN titles_cursor

Fetch next from titles_cursor INTO @ title

WHILE @ FETCH_STATUS = 0
BEGIN
If @ title <> 'id'
Begin
If @ MySql =''
Set @ MySql = @ MySql + @ title
Else
Set @ MySql = @ MySql + ',' + @ title
End
Fetch next from titles_cursor INTO @ title
END

CLOSE titles_cursor

DEALLOCATE titles_cursor
-- End with column name

/* Insert the content of the temporary table to the Table @ sTabelName [local] */
Set @ SQL = 'insert' + @ sTabelName + 'select' + @ MySql + 'from tempTbl'

EXEC (@ SQL)

/* Delete a temporary table */
If exists (select * from dbo. sysobjects where id = object_id (N 'temptbl') and OBJECTPROPERTY (id, N 'isusertable') = 1)
Drop table tempTbl

GO