• sql插入记录到带标识列的表


     

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Product_temp]
    GO

    --创建临时表
    CREATE TABLE [dbo].[Product_temp] (
     [PAutoID] [int] NOT NULL,
     [PName] [varchar](20) NOT NULL,
     [PHasContent] [bit] NULL
    ) ON [PRIMARY]
    GO
    --**************************************************开始: 常数内容 **********************************************************

    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(1,'在线电视',1)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(2,'在线影视',1)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(3,'视频分享',1)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(4,'数字收音机',1)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(5,'流媒体综合首页',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(6,'智能乐园',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(7,'2010世界杯',1)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(8,'VIP专区',1)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(9,'鬼节专区',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(10,'VIP选美专区',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(11,'游戏专区',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(12,'高清电影专区',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(13,'男性健康VIP专区',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(14,'李小龙VIP专区',0)
    --------------------------------------------------------------------------------
    INSERT INTO Product_temp(PAutoID,PName,PHasContent) VALUES(15,'优酷VIP专区',0)
    --------------------------------------------------------------------------------

    --**************************************************结束: 常数内容 **********************************************************


    --**************************************************开始: 更新 **********************************************************

    UPDATE orig SET
    PName=temp.PName,
    PHasContent=temp.PHasContent
    from Product orig, Product_temp temp
    where orig.PAutoID = temp.PAutoID

    GO

    SET IDENTITY_INSERT Product on

    INSERT INTO Product(PAutoID,PName,PHasContent)
    select PAutoID,PName,PHasContent
    from Product_temp patch
    where not exists (select * from Product where PAutoID = patch.PAutoID)

    SET IDENTITY_INSERT Product OFF
    GO
    --**************************************************结束: 更新 **********************************************************

    --删除临时表
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Product_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Product_temp]
    GO

    转载请注明出处:http://blog.csdn.net/dasihg/article/details/6782378

  • 相关阅读:
    《团队-团队编程项目作业名称-成员简介及分工》
    《团队-团队编程项目作业名称-需求分析》
    《结对-结对编项目作业名称-需求分析》
    团队编程项目总结
    并发工具包
    并发工具包
    spring注解的源码解释
    webservice客户端
    线程池
    问题
  • 原文地址:https://www.cnblogs.com/dashi/p/4034778.html
Copyright © 2020-2023  润新知