ms sql存储过程 [英] ms sql store procedure

问题描述

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  <小号pan class =code-keyword> 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.



所以任何人都建议我如何为

解决方案

首先，要将值从一个表插入另一个表，请使用如下查询：

  INSERT   IN TO 合并（Field1，Field2，... FieldN）
  SELECT  Field1，Field2，... FieldN 
  FROM  bse_trade 

在开始将数据插入Consolidation表之前，请检查正确的数据使用 SELECT 语句。

  SELECT  [segment ]， CASE   WHEN  [process1] =  0   THEN   1   ELSE   0   END   AS  [process1]，[memID]，[ traderID]，[scd]，[symbol]，[tradeprice] / 100  AS  [tradeprice]，[tradedqty]，[oppmID]，[opptrID]，[tradetime1] ，[tradetime2]，[accountID]，[trnID]，[trntyp]， CASE   WHEN  [transtype] = '  B'  那么  2   WHEN  [ transtype] = '  S' 那么 < span class =code-digit> 2   END   AS  [transtype]， CONVERT （ VARCHAR （ 10 ），[tradeID] ）+ [tradeType]  AS  NewTrans，[instID]，[isin]，[scrgrp]，[settno]，[orderentrytime]，[locationID] 
 < span class =code-keyword> FROM  bse_trade 

0 down vote favorite


i want to insert record from one table to another but there is 11 conditions for that

below 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 OFF

and below is conditions
1) check the process1 flag in bse_trade table where process1=0
2) Set process1=1 where process1=0
3) set transtype=1 where transtype=B , transtype=2 where transtype=S {where process1=1}
4) set tradeprice =tradeprice/100 where process1=1
5) Concat tradeid + transtype
6) check for same combination in consolidation where segment =BSE
7) insert to consolidated if not exist , update if exist
8) set process1=0 in consolidated if insert
9) set process1=2 in bse_trade on insert or update
10) set tradevalue=tradedqty x tradeprice in consolidated where process1=0
11) set set process1=1 in consolidation.

so any one suggest me how to write procedure for that

解决方案

First of all, to insert values from one table to another, use query like this:

INSERT INTO Consolidation (Field1, Field2, ... FieldN)
SELECT Field1, Field2, ... FieldN
FROM bse_trade 

Before you start inserting data into Consolidation table, please, check proper data with SELECT statement.

SELECT  [segment], CASE WHEN [process1] = 0 THEN 1 ELSE 0 END AS [process1], [memID], [traderID], [scd], [symbol], [tradeprice]/100 AS [tradeprice], [tradedqty], [oppmID], [opptrID], [tradetime1], [tradetime2], [accountID], [trnID], [trntyp], CASE WHEN [transtype] = 'B' THEN 2 WHEN [transtype] = 'S' THEN 2 END AS [transtype], CONVERT(VARCHAR(10), [tradeID]) + [tradeType] AS NewTrans, [instID], [isin], [scrgrp], [settno], [orderentrytime], [locationID]
FROM bse_trade 

这篇关于ms sql存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！