Loop through a table
-
Hi,
I had to display a single row from a table and display it, the next day the next row .................(this is for todays thought)
so i created two tables, one for the data and the other to hold today id and data. I have to loop to the first record after i reach the end of the first table, to polulate the second table i wrote the following code:
DECLARE @id1 int
DECLARE @idLast int
IF (select count(*) from tbl_TodaysThoughtStore) =0
BEGIN
INSERT tbl_TodaysThoughtStore (ThoughtID, Thought, Author)
SELECT TOP 1 ThoughtID, Thought, Author FROM tbl_TodaysThought ORDER By NEWID()
END
ELSE
BEGIN
DECLARE @CreateDate VARCHAR(255)
DECLARE @id INT
SET @CreateDate = (SELECT TOP 1 createdate FROM tbl_TodaysThoughtStore)
SET @id = (SELECT TOP 1 Thoughtid FROM tbl_TodaysThoughtStore)
IF DateAdd(dd,1,@CreateDate) < getdate()
BEGIN
SET @id = (SELECT TOP 1 Thoughtid FROM tbl_TodaysThoughtStore)
DELETE FROM tbl_TodaysThoughtStore
DECLARE abc SCROLL CURSOR FOR SELECT thoughtid FROM tbl_TodaysThought
OPEN abc
FETCH LAST FROM abc INTO @id1
SET @idLast = @id1
FETCH ABSOLUTE 1 FROM abc INTO @id1
-- FETCH NEXT FROM abc INTO @id1
IF @@FETCH_STATUS = 0
BEGIN
IF @id = @id1
BEGIN
IF @idLast = @id1
BEGIN
FETCH ABSOLUTE 1 FROM abc INTO @id1
END
ELSE
BEGIN
FETCH NEXT FROM abc into @id1
END
END
ELSE
BEGIN
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @id = @id1
BEGIN
IF @idLast = @id1
FETCH ABSOLUTE 1 FROM abc INTO @id1
ELSE
FETCH NEXT FROM abc into @id1
-- FETCH NEXT FROM abc into @id1
BREAK
END
ELSE
BEGIN
FETCH NEXT FROM abc into @id1
END
END
END
END
-- SELECT TOP 1 * FROM tbl_TodaysThought WHERE thoughtid = @id1
CLOSE abc
DEALLOCATE abc
INSERT tbl_TodaysThoughtStore (ThoughtID, Thought, Author) SELECT ThoughtID, Thought, Author FROM tbl_TodaysThought WHERE ThoughtID = @ID1
END
END
-- SELECT ThoughtID, Thought, Author FROM tbl_TodaysThought WHERE ThoughtID = @ID1
-- SELECT TOP 1 * FROM tbl_TodaysThoughtStore
GO
But this seems long. Is there a better way to accomplish this?
Thanks
-
can you give table srtucts?
-
CREATE TABLE [tbl_TodaysThought] (
[ThoughtID] [int] IDENTITY (1, 1) NOT NULL ,
[Thought] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_tbl_TodaysThought] PRIMARY KEY CLUSTERED
(
[ThoughtID]
 
ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [tbl_TodaysThoughtStore] (
[ThoughtStoreID] [int] IDENTITY (1, 1) NOT NULL ,
[ThoughtID] [int] NOT NULL ,
[Thought] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF__tbl_Today__Creat__55F4C372] DEFAULT (getdate()),
CONSTRAINT [PK_tbl_TodaysThoughtStore] PRIMARY KEY CLUSTERED
(
[ThoughtStoreID]
 
ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
-
It doesn't appear that your cursor does much more than loop through your table and give you the last ID number. I did not take a very long look at it, but is this true? If so, you do not need a cursor to find the last row ID in a table. Can you give a better explanation of what you are trying to accomplish?
-
I'm not sure what you are trying to do by date. How are you determining what day to use or if you have used this thought already?
-
This may help solve your problem without using a loop and using data normalization to not duplicate data.
--Build a table of numbers, you may want to make it a real table because it is a good tool to use
select top 8001 Identity(int, 1,1) As ID
into #Number
from master.dbo.syscomments a
cross join master.dbo.syscomments b
go
--Create a table for thoughts
create table #Thought (ThoughtID int identity(1,1), Thought varchar(255), Author varchar(255))
insert #Thought values('Number 1', 'Twain')
insert #Thought values('Number 2', 'Twain')
insert #Thought values('Number 3', 'Franklin')
insert #Thought values('Number 4', 'Franklin')
go
--Create #DailyThought table
create table #DailyThought(date datetime, ThoughtID int)
--Populate next 365 days of #DailyThought
insert #DailyThought
select dateadd(dd, n.id, getdate()), thoughtid
from #Thought t
inner join #number n
on ((n.id - 1) % (select max(ThoughtID) from #Thought)) + 1 = t.thoughtid and n.ID < 366
go
--Retrieve thought for each day
select *
from #DailyThought dt
inner join #Thought t
on dt.ThoughtID = t.ThoughtID
go
--Clean up temporary stuff
drop table #DailyThought
drop table #Thought
drop table #Number
go
-
it gets the next row id in the table. not the last.
The task im accomplishing is to get a quote from tbl_Todaysthought table.
say tbl_Todaysthought has 10 rows then after 10 days it should go to the top and get the first row and so on
1, 2, 3, .........10,1,2,3,.......10
the above code works, but i want to know if theres any better method, keeping mind some rows can be deleted, so u cannot just get thoughtID +1 row.
-
howabout you add a column (timesDisplayed) to the ThoughtsStore table for the number of times the thought has been displayed.
insert into todays_thought
select top 1 * from thought_store order by timesDisplayed, createDate desc
I may be backwards with the create date sort. But this way will allow you to "rank" thoughts.
--another way is to set a bit column for lastdisplayedthought. Then you can
select top 1 * from thoughtStore where CreatedDate < (select createdate from thoughtstore where lastdisplayedthought = 1) order by createddate
-
Its a good idea, but i just have to display the data one by one. not allowed to be creative
-
top 1 will insert one row at a time, but youre not going to be able to do this with the existing table structures.
There is no strategy for mapping or retaining information temporally. I mean that you will have no way of determining which day stored which thought, and without knowing what you've done in the past, youre not going to be able to determine what to do next.
There has to be some day or usage statistics to make this work. I like the logic of cursors, but this method looks like you want to leave one open all the time, and that is not good.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply