/****** Object: Table [dbo].[Transactions] Script Date: 25/02/2016 09:41:14 AM ******/ >SET ANSI_NULLS ON >GO > >SET QUOTED_IDENTIFIER ON >GO > >SET ANSI_PADDING ON >GO > >CREATE TABLE [dbo].[Transactions]( > [GUID] [uniqueidentifier] NOT NULL, > [DateImported] [datetime] NULL, > [OID] [nvarchar](max) NULL, > [PNR] [nvarchar](max) NULL, > [StoredFare] [float] NULL, > [RMQ] [nvarchar](max) NULL, > [LowerFare] [float] NULL, > [Timestamp] [datetime] NULL, > [Month] [nvarchar](50) NULL, > [Day] [int] NULL, > [Year] [int] NULL, > [Time] [varchar](50) NULL, > [MFRA] [int] NULL, > [ScriptResult] [varchar](max) NULL, > [AgentID] [varchar](max) NULL, > [PNRStatus] [varchar](max) NULL, > [Segments] [varchar](max) NULL, > [Currency] [varchar](50) NULL, > CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED >( > [GUID] ASC >)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] >) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > >GO > >SET ANSI_PADDING OFF >GO > >ALTER TABLE [dbo].[Transactions] ADD CONSTRAINT [DF_Transactions_GUID] DEFAULT (newid()) FOR [GUID] >GO > >ALTER TABLE [dbo].[Transactions] ADD CONSTRAINT [DF_Transactions_DateImported] DEFAULT (getdate()) FOR [DateImported] >GO>
USE [RESREPRICERTest] >GO > >/****** Object: StoredProcedure [dbo].[InsertTransaction] Script Date: 25/02/2016 09:42:15 AM ******/ >SET ANSI_NULLS ON >GO > >SET QUOTED_IDENTIFIER ON >GO > >-- ============================================= >-- Author: <Author,,Name> >-- Create date: <Create Date,,> >-- Description: <Description,,> >-- ============================================= >CREATE PROCEDURE [dbo].[InsertTransaction] > -- Add the parameters for the stored procedure here > @GUID uniqueidentifier > ,@OID nvarchar(max) > ,@PNR nvarchar(max) > ,@StoredFare float > ,@RMQ nvarchar(max) > ,@LowerFare float > ,@Timestamp datetime > ,@MFRA int > ,@ScriptResult varchar(max) > ,@AgentID varchar(max) > ,@PNRStatus varchar(max) > ,@Segments varchar(max) > ,@Currency varchar(50) > >AS >BEGIN > -- SET NOCOUNT ON added to prevent extra result sets from > -- interfering with SELECT statements. > SET NOCOUNT ON; > > if not exists(Select 1 From [dbo].[Transactions] Where PNR = @PNR AND Timestamp = @Timestamp) > BEGIN > INSERT INTO [dbo].[Transactions] > ([GUID] > ,[OID] > ,[PNR] > ,[StoredFare] > ,[RMQ] > ,[LowerFare] > ,[Timestamp] > ,[MFRA] > ,[ScriptResult] > ,[AgentID] > ,[PNRStatus] > ,[Segments] > ,[Currency]) > VALUES > (@GUID > ,@OID > ,@PNR > ,@StoredFare > ,@RMQ > ,@LowerFare > ,@Timestamp > ,@MFRA > ,@ScriptResult > ,@AgentID > ,@PNRStatus > ,@Segments > ,@Currency) > > END >END > > >GO >>