/****** 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 >>>>