Common techniques of ASP database programming SQL

Source: Internet
Author: User
Tags copy empty insert join sql query table name
Programming | skills | data | database | How to delete a column in a table repeating columns Yes, for instance.

Table [table1]

ID name1 aa2 bb3 cc1 Aa2 cc
I think the last watch is like this.

ID Name 1 AA2 bb3 cc


Reply:

Save the record in a temporary table #t, save a duplicate record, and then save the record in the temporary table #t back into the original table, noting that "SELECT DISTINCT Id,class,name" contains all the fields you need, otherwise some fields are deleted.

Execute the following code in the query manager:

SELECT DISTINCT ID,, name
Into #t
From table1 DELETE table1
INSERT
into table1
SELECT *
From #t
Two. Find people who will both VB and PHP

The table is like this:

ID Employee Skills 1 1 VB2 1 PHP3 1 ASP4 2 PHP5 3 ASP6 4 VB7 4 ASP
From this table to find both VB and PHP, how to write SQL?

Reply:

Select employee from [table] where employee in (select employee from [table] where skill = ' VB ') and skill = ' PHP ' three. Database merge issues

Two tables in Access that want to merge the contents of two tables

The table [a] structure is as follows:

[ID] number autonumber [name] Name text [prices] price number [guige] specification text [Changjia] manufacturer's text [Baozhuang] Wrapping text [Danwei] unit text
A total of 900 records, except the ID and Name fields, all other can be empty

The table [b] structure is as follows:

[ID] number autonumber [name] Name text [prices] price number [Changjia] manufacturer's text [Danwei] unit text [Xingzhi] Nature text
A total of 800 records, in addition to the ID and name fields, less than table [a] a few fields, but also more than one [Xingzhi] of the word Fuan other can be empty

Now you want to generate a new table [C], which is structured as follows, and content is the sum of the contents of two tables.

[ID] number autonumber [name] Name text [prices] price number [guige] specification text [Changjia] manufacturer's text [Baozhuang] packing text [danwei] unit text [Xingzhi] Nature text
Use SQL statements can also, hand-operated or XML, regardless of how, how to achieve ah, the man to be depressed bad, really want us to enter 800 records, I hung up.

Reply:

1. This

Insert into C (id,name,.....)
Select Id,name,.....
From a
Insert into C (id,name,.....)
Select Max (ID) +1,name,.....
From b
2. Corrections:

If you are executing directly in Query Analyzer:

Insert into C (name,.....)
Select Name,.....
From a
Insert into C (name,.....)
Select Name,.....
From b
3. Using the Union method

insert into [c] ([ID], serial number, AutoNumber)
Select [ID], number, AutoNumber from [a]
Union
Select [ID], number, AutoNumber from [b]
4.asp Solution

<% ' cyclic detection table A
Set rs = Server.createobect ("ADODB.") RECORDSET ")
Rs.Open "SELECT * from ' A ORDER by id", conn,1,1
Do as not rs.eof
Call Actadd (RS ("name")) ' calls functions that add content like Table B!
Rs. MoveNext
Loop
Rs. Close
Set rs = Nothing

Sub actadd (TXT)
Dim ts, sql
sql = "INSERT INTO B" (name) VALUES (' & txt & ')
Set ts = conn.execute (SQL)
Ts. Close
Set ts = Nothing
End Sub
%>
5.asp Solution

<%
Dim arr_temp1,arr_temp2,arr_data
Set Rs=conn.execute ("Select Id,name,price,guige,changjia,baozhuang,danwei from a")
Arr_temp1=rs.getrows
Rs.close
Set rs=nothing

Set Rs=conn.execute ("Select Id,name,price,guige,changjia,danwei,xingzhi from B")
Arr_temp2=rs.getrows
Rs.close
Set rs=nothing

REM Start processing
ReDim Arr_data (UBound (arr_temp1,2) +ubound (arr_temp2,2), 7)
REM Copies the contents of the two arrays.
This part of myself wrote to do two loops
And then save it in the database.
%> finally turn some of the classic SQL statements:

1. Frog Frog Recommendation: Some subtle SQL statements

Description: Copy table (copy only structure, source table name: A new table name: b)

Sql:select * into B from a where 1 <> 1
Description: Copy 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: Displays the article, the author, and the 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
Description: Outer join query (table name 1:a table name 2:b)

Sql:select a.a, A.B, A.C, B.C, B.D, B.f from a left-out JOIN b on a.a = B.C
Description: Schedule five minutes advance reminder

Sql:select * from schedule where DateDiff (' minute ', F start time, GETDATE ()) >5
Description: Two related tables, delete the information in the primary table that has not been in the secondary table

Sql:

Delete from info where NOT exists (Sele



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.