ms sql存储过程

本文介绍了ms sql存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 0下来投票最爱 i希望从一张桌子到另一张桌子插入记录但是有11个条件 以下是我的桌子 USE [dealheet] GO / * *对象：表[dbo]。[bse_trade] ** / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo] 。[bse_trade]（[segment] varchar NULL ，[process1] [ int ] NULL ，[memID] [ int ] NULL ，[traderID] [ bigint ] NULL ，[scd] [ bigint ] NULL ，[symbol] varchar NULL ，[tradeprice] [ money ] NULL ，[tradedqty] [ bigint ] NULL ，[oppmID] [ int ] NULL ，[opptrID] [ int ] NULL ，[tradetime1] varchar NULL ，[tradetime2] varchar NULL ，[accountID] varchar NULL ，[trnID] [ bigint ] NULL ，[trntyp] varchar NULL ，[transtype ] varchar NULL ，[tradeID] [ bigint ] NULL ，[instID] varchar NULL ，[isin] varchar NULL ，[scrgrp] varchar NULL ，[settno] varchar NULL ，[orderentrytime] varchar NULL ，[locationID] varchar NULL ） ON [ PRIMARY ] GO SET ANSI_PADDING OFF ========================== USE [数据库名称] GO / * 对象：表[dbo]。[合并] * / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE 表 [dbo]。[合并]（[段] varchar NULL ，[tradeID] [ bigint ] NULL ，[accountID] varchar NULL ，[symbol1] varchar NULL ，[series1] varchar NULL ，[tradedqty] [ bigint ] NULL ，[tradeprice] [ money ] NULL ，[transtype] [ int ] NULL ，[tradetime] varchar NULL ，[traderID] [ bigint ] NULL ，[locationID] varchar NULL ，[tradevalue] [ money ] NULL ，[orderentrytime] varchar NULL ，[process1] [ int ] NULL ，[isin] varchar NULL ） ON [ PRIMARY ] GO SET ANSI_PADDING OFF 及以下是条件 1）检查process1 fla g在bse_trade表中，其中process1 = 0 2）设置process1 = 1其中process1 = 0 3）set transtype = 1其中transtype = B，transtype = 2其中transtype = S {where process1 = 1} 4）set tradeprice = tradeprice / 100 where process1 = 1 5）Concat tradeid + transtype 6）在合并中检查相同的组合，其中segment = BSE 7）如果不存在则插入合并，如果存在则更新 8）在合并中设置process1 = 0如果插入 9）在插入或更新时在bse_trade中设置process1 = 2 10）在合并中设置tradevalue = tradedqty x tradeprice，其中process1 = 0 11）在合并中设置set process1 = 1. 所以任何人都建议我如何为解决方案 0 down vote favoritei want to insert record from one table to another but there is 11 conditions for thatbelow is my tables USE [dealsheet] GO /** Object: Table [dbo].[bse_trade] **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[bse_trade]( [segment] varchar NULL, [process1] [int] NULL, [memID] [int] NULL, [traderID] [bigint] NULL, [scd] [bigint] NULL, [symbol] varchar NULL, [tradeprice] [money] NULL, [tradedqty] [bigint] NULL, [oppmID] [int] NULL, [opptrID] [int] NULL, [tradetime1] varchar NULL, [tradetime2] varchar NULL, [accountID] varchar NULL, [trnID] [bigint] NULL, [trntyp] varchar NULL, [transtype] varchar NULL, [tradeID] [bigint] NULL, [instID] varchar NULL, [isin] varchar NULL, [scrgrp] varchar NULL, [settno] varchar NULL, [orderentrytime] varchar NULL, [locationID] varchar NULL ) ON [PRIMARY]GO SET ANSI_PADDING OFF==========================USE [database name] GO /* Object: Table [dbo].[consolidated] */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[consolidated]( [segment] varchar NULL, [tradeID] [bigint] NULL, [accountID] varchar NULL, [symbol1] varchar NULL, [series1] varchar NULL, [tradedqty] [bigint] NULL, [tradeprice] [money] NULL, [transtype] [int] NULL, [tradetime] varchar NULL, [traderID] [bigint] NULL, [locationID] varchar NULL, [tradevalue] [money] NULL, [orderentrytime] varchar NULL, [process1] [int] NULL, [isin] varchar NULL ) ON [PRIMARY]GO SET ANSI_PADDING OFFand below is conditions1) check the process1 flag in bse_trade table where process1=02) Set process1=1 where process1=03) set transtype=1 where transtype=B , transtype=2 where transtype=S {where process1=1}4) set tradeprice =tradeprice/100 where process1=15) Concat tradeid + transtype6) check for same combination in consolidation where segment =BSE7) insert to consolidated if not exist , update if exist8) set process1=0 in consolidated if insert9) set process1=2 in bse_trade on insert or update10) set tradevalue=tradedqty x tradeprice in consolidated where process1=011) set set process1=1 in consolidation.so any one suggest me how to write procedure for that 解决方案 这篇关于ms sql存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！