SQL Server - Insert one record once a month using a sp

Asked By Daniel on 19-Jun-12 02:54 PM
Earn up to 10 extra points for answering this tough question.
Hi! I have 2 tables 

CREATE TABLE Fund (
    [FundID] INT IDENTITY(1,1) PRIMARY KEY
    , [Amount] INT
)

CREATE TABLE Transaction (
    [TransactionID] INT IDENTITY(1,1) PRIMARY KEY
    , [TransactionType] NVARCHAR(255)
   ,[Date] DATETIME
   , [FundID] INT
    )


As you can see this tables one is the parents and another is the child.
What I need is just to insert using a stored procedure many records but the stored procedure must have a control that shoul not accept duplicate [FundID] to make a transaction in same month.It can be inserted many but once per each month.Means when I am inserting a in a single month records  in the transaction Table the stored procedure should first verify if the fundID is not already been inserted.
Any Idea please I dont know where to start from.
Thanks!
S K replied to Daniel on 19-Jun-12 11:59 PM
Best way is you can use Window scheduler or Sql Job to run the stored proc once in a month and in that sp you can insert one record which you want. So in that case the sp inserted only one record in a month.
Daniel replied to S K on 20-Jun-12 12:07 AM
Sorry may be I am explain it in wrong way...I wanted to say that the stored procedure can insert many row but should control the foreign key  if it is not inserted twice in a single month.
TSN ... replied to Daniel on 20-Jun-12 01:13 AM

Here is the stored procedure that doesn’t allow you multiple fund id in the Same month.

Try this

Create procedure MyProcedure1

(

@ myDate Datetime,

@FundID int,

@TransactionType nvarchar(50)

)

As Begin

If Exist(select * from Transaction where FundID=@FundId and (YEAR(Date) = YEAR(myDate) AND MONTH(Date) = MONTH(@myDate)) )

Begin

Print Record for the Fund ID Exists in the Current Month

End

Else

begin

Insert into Transaction(TransactionType,Date,FundId) values(@TransactionType,@myDate,@FundId)

End

Try this and let me know any issues

C D replied to Daniel on 29-Jun-12 01:17 AM

Hi Frndz,

 

Functionality:  Check Month Exist

 

 

To achieve this task,

 

Make one function that  check month and fundid record exist or not.

 

Check VerifyExistData function

 

After then make one SP and pass paramerter Fundid, TransactionType

 

Now check Exist month for particular ID or not

 

SET @Flag = dbo.VerifyExistData(@FundID)

 

Check below function and SP

 

 

 

Full Logic     :

 

Function for check Month Record Exist or not

 

CREATE FUNCTION [dbo].[VerifyExistData]

(

  @FundID INT

)

RETURNS BIT

AS

    BEGIN

        DECLARE @rtnValue as BIT = 1

        IF EXISTS

        (

          Select

              [Date]

          from

              [Transaction]

          where [FundID] = @FundID

              AND DatePart(month, [Date]) = DATEPART(month,getdate())

              AND DatePart(year, [Date]) = DATEPART(YEAR,getdate())

             

        )

        BEGIN

          SET @rtnValue = 0;

        END

        RETURN  @rtnValue ;

    END

 

 

Stored Procedure for Insert Record into Transaction Table

 

CREATE PROCEDURE [dbo].[InsertTransaction]

    @FundID AS INT,

    @TransactionType AS Varchar(255)

AS

 

Declare @Flag  AS BIT

 

SET @Flag = dbo.VerifyExistData(@FundID)

 

IF (@Flag = 1)

BEGIN

    INSERT INTO [Transaction](FundID,TransactionType,[Date]) values (@FundID,@TransactionType,GETDATE())

END

 

 

Hope this helpful!

Thanks

 

 

 

help
Create nested stored procedure help SQL Server
I know I am missing something simple but all I want to do is create a stored procedure that will create a table and add data to it on demand. Basically all I want to do is the following: Stored Proc1 Create Table A. . . Insert Data using a date field. . . Stored Proc2 Drop Table A So here
Create table from results of stored procedure? SQL Server
Hello. Would anyone know how to automatically create a new table (not having to define each column and datatype) from a result set of a stored procedure that comes from another sql server that's being linked? Thanks in advance. J SQL Server Programming Discussions CREATE TABLE (1) Table (1) Stored procedure (1) Create (1) Datetime (1) Column (1) Procedure (1) Money
this is my state table: create table tbl_state(id bigint primary key identity, state nvarchar(max)) this is my city table: create table tbl_city(id bigint primary key identity, state bigint, city nvarchar(max)) if i selected dropdownlist1 you need to specify dataTextFileds and Datavalue Fileds ddlCity.DataSource = / / DataFrom the Database which is stored in the Dataset ddlCity.DataTextField = "City" ; ddlCity.DataValueField = " identityValue " ; ddlCity.DataBind(); thank you. . . then what Bind grid here } keywords: GridViewPageEventArgsSqlDataAdapter, Error, nvarchar, bigint, PageIndexChanging description: Error this is my state table: create table tbl_state(id bigint primary key identity, state nvarchar(max)) this is my city
Create table and drop table owner defaults SQL Server
Hi, When I have the Create table in a Stored proc, the default user is the userid of the person logged in but when I go to drop the table, it defaults to dbo. How can I obtain the userid that the table is created with from within the stored proc so that I can add it into
i want to create table and the name of the table should be the value from database. . . . is that possible end of post u can create table at runtime by using proper create table syntax only the matter is if u stored table name in another existing table in database then u just put table name from
Temp table creation SQL Server
Are there any differences when creating a local temp table using the syntax create table #temp to create table tempdb. . #temp thanks in advance Ralph SQL Server Programming Discussions SQL Server (1) Module (1 Database (1) Variablesinstead (1) Permananent (1) Tempwill (1) Tempdb (1) Dang (1) Yes, create table #temp will create a local temporary table in tempdb that can only be accessed by
create table statement SQL Server
I need to create duplicates of certain tables. It woud be great if i can find the 'create table statement', in any of the system tables which the Create Table of the Object Browser in Query Analyzer users, so that i can just change the name and create a new table thus. Please help me find the Create Table statement available in the
How to implement multiple assignment? SQL Server
Consider the following three tables: CREATE TABLE PersonInfo ( ID INTEGER NOT NULL PRIMARY KEY) ; CREATE TABLE Earns ( ID INTEGER NOT NULL PRIMARY KEY REFERENCES PersonInfo) ; CREATE TABLE SalaryUnknown ( ID INTEGER NOT NULL PRIMARY KEY REFERENCES PersonInfo) ; CREATE TABLE Unsalaried ( ID INTEGER NOT NULL PRIMARY KEY REFERENCES PersonInfo) ; Noting that SQL Server does not
hi friends, am new to creating tables on in my project? so i need some tips from u all to create a efficient table structure using different types? thanks in advance. . . . . Hello, CREATE TABLE table_name ( { < column_definition > | < table_constraint > } [ , . . .n ] ) < column_definition > :: = { column_name data_type } [ { DEFAULT constant_expression | [ IDENTITY [ ( seed , increment ) ] ] } ] [ ROWGUIDCOL ] [ < column_constraint > [ . . .n DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] } The following examples show how to: • Create a two-column table with an identity column as the PRIMARY KEY. • Create a one-column table with a PRIMARY KEY constraint • Create a table with one of
A difficult query to put columns into rows. SQL Server
I have a table: Create table UserAircraftComponent ( id INT identity(1, 1) NOT NULL, UserAircraftid INT, ComponentId INT ) This table holds all the comonents libnked to a UserAircraft. ComponentId is a lookup on a Component table (id), and UserAircraftId links to a UserAicraft table (id). Component table looks something like this: Create table Component ( id int identity(1, 1) NOT NULL, componenttypeid INT description varchar(30) ) It