How to Perform in this Query Procedure. [Resolved]

Posted by Jayakumars under C# on 1/24/2017 | Points: 10 | Views : 390 | Status : [Member] [MVP] | Replies : 2
Hi

How to insert this situation using Cursor loop

when i enter from date and to date . I need with in the date data inserted using cursor.

How will do this

My From date : 30 Dec 2016
My To Date : 24 jan 2017


tables this

create table employees1
(
Id int primary key identity(1,1),
Ename varchar(40),
Empno int,
empcreateddate datetime
)

create table employees2
(
Id int primary key identity(1,1),
Ename varchar(40),
Empno int,
empcreateddate datetime
)


insert into employees2 values('John',100,getdate()),('Elin',101,getdate()-1),('Revghy',102,getdate()),
('Rufen',103,getdate()),('Fedrick',104,getdate()-2),('Adam',105,getdate()),('Gilic',106,getdate()-1),
('Fernadas',107,getdate()),('Fernadas sr',107,getdate()-23),('jernadas',107,getdate()-24),
('yjernadas',107,getdate()-25)



--truncate table employees2


select * from employees2 order by empcreateddate desc


--I Need to insert this table employees1 using cursor looping from date to date
records only. But from this table employees2

-- here My employees1 No datas right row. so
-- I want how to insert records based on created date using cursor
--for ex: i need first 24 jan 2017 etc ... 30 Dec 2016
-- How to use cursor looping for insert

Mark as Answer if its helpful to you


Responses

Posted by: A2H on: 1/24/2017 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
If you still want a cursor code then you can use below code

Declare @Ename Varchar(40), @empno int,@empcreateddate datetime
DECLARE employees_cursor CURSOR FOR
SELECT Ename,Empno,empcreateddate FROM employees2 WHERE empcreateddate BETWEEN '30 Dec 2016' AND '23 jan 2017' ORDER BY empcreateddate DESC

OPEN employees_cursor
FETCH NEXT FROM employees_cursor INTO @Ename, @empno, @empcreateddate

WHILE @@FETCH_STATUS = 0
BEGIN
Insert into employees1(Ename,Empno,empcreateddate)
VALUES( @Ename,@empno,@empcreateddate)

FETCH NEXT FROM employees_cursor INTO @Ename, @empno, @empcreateddate
END

CLOSE employees_cursor
DEALLOCATE employees_cursor


I would not recommend using cursor as it has disadvantages like performance etc

Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 1/24/2017 [Member] [MVP] Silver | Points: 25

Up
0
Down
If your requirement is to insert the data in to Employees1 table based on the from and to date, then you may not need to use cursor. You can use the Insert Into statement with Select Query like below
INSERT INTO Employees1
SELECT Ename,Empno,empcreateddate FROM employees2 WHERE empcreateddate BETWEEN '30 Dec 2016' AND '23 jan 2017' ORDER BY empcreateddate DESC


Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response