Solved

sql query to insert the fibonacci sequence into a table

Posted on 2013-12-31
2
464 Views
Last Modified: 2014-02-19
I need an insert statement to insert the fibonacci sequence into a table. Lets think for a moment that the table has two fields

fibid - is an integer, identity field
fibnum is where the fibonacci sequence should go.

so it will be something like this

fibid(1,2,3,4,5)
fibnum(1,2,3,5,8)

etc

lets say populate 200 records

thanks,
Vinnie
0
Comment
Question by:damixa
2 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39749180
There are many ways to solve this problem, with different performances.....

But for a novice and easy to understand the below is the best approach

Step 1 : Create a table fibonacci with the required fields
step 2 : create a loop
Step 3 : caliculate the current fibonacci number
step 4: Store it in the table

DECLARE @T TABLE
(
fbid BIGINT IDENTITY(1,1)
,Fib BIGINT
)

DECLARE @FibonacciCurrent BIGINT
DECLARE @FibonacciPast BIGINT
DECLARE @Temp BIGINT
DECLARE @Counter BIGINT
SET @FibonacciCurrent = 1
SET @FibonacciPast = 1
WHILE (@Counter < 200)
BEGIN
   INSERT INTO @t (fib)
   SELECT @FibonacciCurrent

   SET @temp = @FibonacciCurrent  
   SET @FibonacciCurrent = @FibonacciCurrent + @FibonacciPast
   SET @FibonacciPast = @temp

   SET @Counter = @counter + 1
END

SELECT * FROM @T

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39754568
check this.
;WITH Fibonacci(n, f, f1)
AS (
        -- This is the anchor part
        -- Initialize level to 1 and set the first two values as per definition
        SELECT  CAST(1 AS BIGINT),
                CAST(0 AS BIGINT),
                CAST(1 AS BIGINT)

        UNION ALL

        -- This is the recursive part
        -- Calculate the next Fibonacci value using the previous two values
        -- Shift column (place) for the sum in order to accomodate the previous
        -- value too because next iteration need them both
        SELECT  n + 1,
                f + f1,
                f
        FROM    Fibonacci
        -- Stop at iteration 93 because we than have reached maximum limit
        -- for BIGINT in Microsoft SQL Server
        WHERE   n < 93
)
-- Now the easy presentation part
SELECT  n,
        f AS Number
FROM    Fibonacci

Open in new window

http://sqlblog.com/blogs/peter_larsson/archive/2009/10/18/simple-fibonacci-calculation.aspx
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to fix this error 14 46
SQL server 2008 SP4 29 34
t-sql month question 8 43
SQL Script to find duplicates 16 19
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now