now iam trying for second table i am just trying to insert store proc automatically it has to increase date an and also reduce count and output like this table2
syst1 syst duedate duedatecount
1 1 2012-04-18 15
2 1 2012-04-19 14
3 1 2012-04-20 13
4 1 2012-04-21 12
5 1 2012-04-22 11
..
..
15 1 0 0
i tried insert query
insert into dbo.tblTable2
(
syst1
syst,
duedate,
duedatecount
ALTER PROCEDURE [dbo].[Insert]
@SportsId NVARCHAR(MAX),
@CSId NVARCHAR(MAX),
@LeagueId NVARCHAR(MAX),
--@CodeTeamA NVARCHAR(MAX),
--@CodeTeamB NVARCHAR(MAX),
@TeamA NVARCHAR(MAX),
--@TeamB NVARCHAR(MAX),
@MatchYear NVARCHAR(MAX),
@UserId NVARCHAR(MAX),
@SportsName NVARCHAR(MAX),
@CSName NVARCHAR(MAX),
@LeagueName NVARCHAR(MAX)
AS
--GET TABLE DETAILS FROM SPORTS TABLE--
BEGIN
DECLARE @reset bit,@MatchCode NVARCHAR(MAX),@EventName NVARCHAR(MAX),@EventTime NVARCHAR(MAX),@MatchCodeUnique NVARCHAR(MAX)
--SEARCH MATCH EXIST OR NOT FOR SPECIFIC LEAGUE OF DESIRED TEAM--
SELECT * FROM RacingEvents WHERE(EventName=@TeamA AND LeagueId=@LeagueId AND CSId=@CSId AND convert(nvarchar(20), convert(datetime,EventDateTime ),103) = convert(nvarchar(20),convert(datetime ,@MatchYear),103))
IF(@@ROWCOUNT=0)
BEGIN
--INSERT CONSTANT MATCH DATA INTO ALL RESULT TABLE--
INSERT INTO RacingEvents(EventId,SportsId,CSId,LeagueId,EventName,EventDateTime,CreatedDate,CreatedBy,SportsName,CSName,LeagueName)values(@MatchCodeUnique,@SportsId,@CSId,@LeagueId,@TeamA,@MatchYear,GETDATE(),@UserId,@SportsName,@CSName,@LeagueName)
--Get Last Inserted record
SELECT TOP(1) @MatchCode=EventId,@EventName=EventName,@EventTime=EventDateTime FROM RacingEvents ORDER BY EventId DESC
-- Fatch all the prediction and subprediction Inserted under the League and Using cursur to enter supprediction Name under the event added
BEGIN
Declare InsertSubPrediction_Cursor Cursor for select SubPredictionId,PredictionId,SubPredictionName from RacingSportsSubPrediction WHERE SportsId=@SportsId AND CSId=@CSId AND LeagueId=@LeagueId
Declare @SubPredId nvarchar(MAX),@SubPredName nvarchar(MAX),@PredId nvarchar(MAX)
OPEN InsertSubPrediction_Cursor
FETCH NEXT FROM InsertSubPrediction_Cursor INTO @SubPredId,@PredId,@SubPredName
WHILE @@FETCH_STATUS = 0
BEGIN
select @PredId
declare @PredName NVARCHAR(MAX)
SELECT @PredName=PredictionName FROM RacingSportsPrediction WHERE PredictionId=@PredId
SELECT @PredName
--Insert Command to insert the record
INSERT INTO RacingSportsOddsAndResult(EventId,PredictionId,SubpredictionId,SubPredictionName,Result,PredictionName,SportsName,CSName,LeagueName,SportsId,CSId,LeagueId,CreatedDate,CreatedBy,EventName ,EventTime) VALUES (@MatchCode,@PredId,@SubPredId,@SubPredName,'awaiting',@PredName,@SportsName,@CSName,@LeagueName,@SportsId,@CSId,@LeagueId, GETDATE(),@UserId,@EventName,@EventTime )
FETCH NEXT FROM InsertSubPrediction_Cursor INTO @SubPredId,@PredId,@SubPredName
END
close InsertSubPrediction_Cursor
deallocate InsertSubPrediction_Cursor
END
SET @reset = 1
END
ELSE
BEGIN
SET @reset = 0
END
SELECT @reset
END
Try this way
Be the Best.........
Marked as answer by Chen Yu - MSFT on Apr 23, 2012 06:46 AM
hi thanks a lot all of u ( kimkosta06,Subalakshmi,Sanjay Verma)
and i find the solution by using ;WITH CTE
in the store proc i just insert values for the two table in a single store proc
on of our friend gave idea to use cursor also
iam trying that thing also in a another store proc
thanks for giving new idea
Sanjay Verma u posted a store proc using cursor and iam trying in that way also
Marked as answer by Chen Yu - MSFT on Apr 23, 2012 06:46 AM
sivaganesh12...
Member
227 Points
308 Posts
if it is possible to do insert store proc for this table like this?
Apr 18, 2012 08:28 AM|LINK
for example 2 tables are there
these are the tables table1
CREATE TABLE [dbo].[tblTable1](
[syst] [int] IDENTITY(1,1) NOT NULL,
[duedatefrom] [datetime] NULL,
[todate] [datetime] NULL,
[duedatecount] [int] NULL,
PRIMARY KEY CLUSTERED
(
[syst] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
tables table2
CREATE TABLE [dbo].[tblTable2](
[syst1] [int] IDENTITY(1,1) NOT NULL,
[syst] [int] NULL,
[duedate] [datetime] NULL,
[duedatecount] [int] NULL,
PRIMARY KEY CLUSTERED
(
[syst1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblTable2] WITH CHECK ADD FOREIGN KEY([syst])
REFERENCES [dbo].[tblTable1] ([syst])
GO
the output of the first table is like this iand i insertedthe value
the output for the first table is table1
syst duedatefrom todate duedatecount
1 2012-04-18 13:20:59.920 2012-05-03 13:20:59.920 15
now iam trying for second table i am just trying to insert store proc automatically it has to increase date an and also reduce count and output like this table2
syst1 syst duedate duedatecount
1 1 2012-04-18 15
2 1 2012-04-19 14
3 1 2012-04-20 13
4 1 2012-04-21 12
5 1 2012-04-22 11
..
..
15 1 0 0
i tried insert query
insert into dbo.tblTable2
(
syst1
syst,
duedate,
duedatecount
)
select
dateadd(day,15,GETDATE())
datediff(day,duedate,getdate())
select datediff(day,'2007-12-15','2008-12-15')
kimkosta06
Member
270 Points
50 Posts
Re: if it is possible to do insert store proc for this table like this?
Apr 18, 2012 12:27 PM|LINK
reviewed your sql query, to create to table and inserting data into it. but whats your exact problem? what you want do? please explain little more.
subalakshmi....
Member
597 Points
130 Posts
Re: if it is possible to do insert store proc for this table like this?
Apr 18, 2012 01:30 PM|LINK
Hi Siva,
You can insert data from another table and can apply calculation. I cant find your business logic. But your insert query syntax is not correct
insert into dbo.tblTable2
(
syst1
syst,
duedate,
duedatecount
)
select
dateadd(day,15,GETDATE())
datediff(day,duedate,getdate())
select datediff(day,'2007-12-15','2008-12-15')
Change the above as
insert into dbo.tblTable2 ( syst1 syst, duedate, duedatecount ) select dateadd(day,15,GETDATE()), datediff(day,duedateFrom,getdate()), datediff(day,'2007-12-15','2008-12-15') From table1One more doubt, In select query are have to use
Cognizant
sanjayverma_...
Participant
1410 Points
330 Posts
Re: if it is possible to do insert store proc for this table like this?
Apr 18, 2012 01:45 PM|LINK
Hi,
Use Cursor to Insertrecord form one table to another.
Cursur will help you to fatch record row wise and allow you too insert into another table.
sivaganesh12...
Member
227 Points
308 Posts
Re: if it is possible to do insert store proc for this table like this?
Apr 19, 2012 04:28 AM|LINK
hi subalakshmi
i tried to insert table1,table2 at a time
actually i have to pass parameter for that like this
declare @syst int
insert into dbo.tblTable2
(
syst,
duedate,
duedatecount
)
select
syst,
duedatefrom as'duedate',
duedatecount
From
dbo.tblTable1
where
syst=@syst
atfter that i have to update the table2 in a single storeproc
sanjayverma_...
Participant
1410 Points
330 Posts
Re: if it is possible to do insert store proc for this table like this?
Apr 19, 2012 06:10 AM|LINK
Hi sivaganesh1234,
Try this way
sivaganesh12...
Member
227 Points
308 Posts
Re: if it is possible to do insert store proc for this table like this?
Apr 19, 2012 01:39 PM|LINK