SELECT INTO 和 IN…

SELECT INTO 和 INSERT INTO SELECT 两种表复制语句 
 Insert是T-sql中常用语句,Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中,经常会遇到需要表复制的情况,如将一个table1的数据的部分字段复制到table2中,或者将整个table1复制到table2中,这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。

      1.INSERT INTO SELECT语句

      语句形式为:Insert into Table2(field1,field2,...) select value1,value2,... from Table1

      要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。示例如下:

--1.创建测试表
    create TABLE Table1
    (
        a varchar(10),
        b varchar(10),
        c varchar(10),
        CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]

    create TABLE Table2
    (
        a varchar(10),
        c varchar(10),
        d int,
        CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]
    GO
    --2.创建测试数据
    Insert into Table1 values('赵','asds','90')
    Insert into Table1 values('钱','asds','100')
    Insert into Table1 values('孙','asds','80')
    Insert into Table1 values('李','asds',null)
    GO
    select * from Table2

    --3.INSERT INTO SELECT语句复制表数据
    Insert into Table2(a, c, d) select a,c,5 from Table1
    GO

    --4.显示更新后的结果
    select * from Table2
    GO
    --5.删除测试表
    drop TABLE Table1
    drop TABLE Table2

    2.SELECT INTO FROM语句

      语句形式为:SELECT vale1, value2 into Table2 from Table1

      要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。示例如下:
--1.创建测试表
    create TABLE Table1
    (
        a varchar(10),
        b varchar(10),
        c varchar(10),
        CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]
    GO

    --2.创建测试数据
    Insert into Table1 values('赵','asds','90')
    Insert into Table1 values('钱','asds','100')
    Insert into Table1 values('孙','asds','80')
    Insert into Table1 values('李','asds',null)
    GO

    --3.SELECT INTO FROM语句创建表Table2并复制数据
    select a,c INTO Table2 from Table1
    GO

    --4.显示更新后的结果
    select * from Table2
    GO
    --5.删除测试表
    drop TABLE Table1
    drop TABLE Table2

                                                            QQ:858084865 Email:lsfhack@163.com

Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14' [Microsoft][ODBC Microsoft Access 驱动程序] FROM 子句语法错误。 /index.asp,行 55;代码是:<%@LANGUAGE="VBSCRIPT"%> <!--#include file="Connections/shujuku.asp" --> <% Dim MM_editAction MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME")) If (Request.QueryString <> "") Then MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString) End If ' boolean to abort record edit Dim MM_abortEdit MM_abortEdit = false %> <% If (CStr(Request("MM_insert")) = "forml") Then If (Not MM_abortEdit) Then ' execute the insert Dim MM_editCmd Set MM_editCmd = Server.CreateObject ("ADODB.Command") MM_editCmd.ActiveConnection = MM_shujuku_STRING MM_editCmd.CommandText = "INSERT INTO [信息表] ([姓名], [性别], [手机号码], [邮箱]) VALUES (?, ?, ?, ?)" MM_editCmd.Prepared = true MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 201, 1, -1, Request.Form("姓名")) ' adLongVarChar MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 201, 1, -1, Request.Form("性别")) ' adLongVarChar MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 201, 1, -1, Request.Form("手机号码")) ' adLongVarChar MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, -1, Request.Form("邮箱")) ' adLongVarChar MM_editCmd.Execute MM_editCmd.ActiveConnection.Close ' append the query string to the redirect URL Dim MM_editRedirectUrl MM_editRedirectUrl = "提示插入成功.html" If (Request.QueryString <> "") Then If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString Else MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString End If End If Response.Redirect(MM_editRedirectUrl) End If End If %> <% Dim Recordset1 Dim Recordset1_cmd Dim Recordset1_numRows Set Recordset1_cmd = Server.CreateObject ("ADODB.Command") Recordset1_cmd.ActiveConnection = MM_shujuku_STRING Recordset1_cmd.CommandText = "SELECT * FROM [信息表]" Recordset1_cmd.Prepared = true Set Recordset1 = Recordset1_cmd.Execute Recordset1_numRows = 0 %> <html> <head> <title>shouye</title> <body><form name="forml" action="<%=MM_editAction%>" method="POST" id="forml"> <p>姓名: <label for="textfield"></label> <input type="text" name="姓名" id="姓名"> </p> <p>性别: <input type="radio" name="性别" id="性别" value="男"> 男 <label for="radio"></label> <input type="radio" name="性别" id="性别" value="女"> 女 <label for="radio2"></label> </p> <p>手机号码: <label for="textfield3"></label> <input type="text" name="手机号码" id="手机号码"> </p> <p>邮箱: <label for="textfield4"></label> <input type="text" name="邮箱" id="邮箱"> </p> <p> <input type="submit" name="button" id="button" value="提交"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="index.asp">取消</a></p> <p>&nbsp;</p> <p>&nbsp;</p> </form> <p>This is my first website<p> </body> </head> </html> <% Recordset1.Close() Set Recordset1 = Nothing %>
最新发布
05-23
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

jarry.liu

如果对您有帮助,鼓励下博主吧

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值