Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Exclamation Unanswered: would like to avoid cursor, please help

    Hi,

    I need to query a database for a recordset and insert this into another database row-by-row.

    For each record I want to provide a time of insert, but for each insert this time has to be incremented by 1, the time format is HH:MMS.0000 so for each inserted record the last decimal point would increment by 1.

    Can I do this without a cursor ?

    Any additional info I will provide gladly.

    Thanks!

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    yea,you can do that...just provide some more details about you tables..plz read the sticky at the top most post of this SQL SERVER forum.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Mar 2003
    Posts
    223
    Create a temp table with identity column. Insert data to this temp table from your query. Then insert to target table by a WHILE LOOP based on that temp table.

    ZYT

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Why would you want to increment each insert by .0001 in a timestamp field?

    If this is what you truely want to do an you wish to manipulate each record as an individual unit I see only one other solution.

    Using another table insert a fixed timestamp and an identity field 1,1 then perform math on the timestamp field using the identity value

    good luck figuring the exact math out

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by limey
    Hi,

    I need to query a database for a recordset and insert this into another database row-by-row.

    For each record I want to provide a time of insert, but for each insert this time has to be incremented by 1, the time format is HH:MMS.0000 so for each inserted record the last decimal point would increment by 1.

    Can I do this without a cursor ?

    Any additional info I will provide gladly.

    Thanks!
    Why would you ever want to do this? If you think this will make your datetime value unique, you are mistaken.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    AND Microseconds are based on the CPU clock, which is only as fast as, I believe .333 ms...I believe it would cconvert them anyway...I'll do a test

    Read the sticky.

    Especially read #1...what are you trying to do...no tech talk...business terms
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Posts
    26

    Exclamation

    Hi,

    Thank you for your replies. Sorry that I was not more precise in my initial post. I will try to elaborate here.

    - I need to create something that will run nightly

    - I need to fetch a number of records (approx 500) based on getdate()

    - Assign variables to the columns returned in the query

    - Do a set of inserts (always 6 inserts)

    - These 6 inserts are a single transaction where a service will fetch this.

    - To distinguish between each transaction it the service uses the time-stamp and increments by one so that each transaction is unique.

    Here is some sql I have worked on thus far:

    -- query
    SELECT col1, col2, col3
    FROM table1
    WHERE col4 = date

    -- assign variables
    @col1 char(64)
    @col2 char(64)
    @col3 char(64)
    @timestamp char(16)

    -- insert
    INSERT INTO table2 VALUES (@col1, @col2, @col3, 'employee', @timestamp)
    INSERT INTO table2 VALUES (@col1, @col2, @col3, 'department', @timestamp)
    INSERT INTO table2 VALUES (@col1, @col2, @col3, 'ID', @timestamp)
    INSERT INTO table2 VALUES (@col1, @col2, @col3, 'website', @timestamp)
    INSERT INTO table2 VALUES (@col1, @col2, @col3, 'spouse', @timestamp)
    INSERT INTO table2 VALUES (@col1, @col2, @col3, 'gender', @timestamp)

    -- Then I would need to fetch the next row based on the query, etc.

    I hope this better explains what I am trying to do.

    Thanks!
    Last edited by limey; 10-02-06 at 19:12.

  8. #8
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    It looks to me that you are attempting to create an EAV table.

    First off why? (I have been attempting to take this type of table and normalize it)

    It looks to me that you are attempting to use a cursor to accomplish this.

    I would suggest that if this is your need that you alter your table and include a field that groups the 6 rows using a and use the timestamp as informational only. Or if you wish to use the timestamp field you run each cursor loop with a WAIT after each insert.

    However I think that as it has been drubbed into me and I agree with using this cursor based approach is not a good Idea if it can be avoided.

    Attempt a insert using an identity column to distinquish individual records.

    By the way your variable declaration has you @timestamp as char 16
    NOT GOOD. VERY VERY BAD.

    timestamps are stored internally as i think FLOAT or Real values.

  9. #9
    Join Date
    Jan 2004
    Posts
    26
    HI rbackmann,

    Thank you for your reply.

    I just looked up what an EAV table is:
    "Entity-Attribute-Value model (EAV) is a data model in which one row stores a single fact"

    This is exactly what I am trying to accompish.

    This table is used by a service to identify a single transaction. Once the data is gathered for the transaction, it is subsequently deleted.

    Yes I think that the cursor will work here, I am not sure of another way to accomplish the task.

    I cannot modify the schema unfortunately. I was hoping to take a getdate() that will output HH:MM.SSS and then add a counter and concatenate it to the end, so that for each fetch, the output will always have a unique timestamp.

    I would avoid the cursor if I could, but I do not have enough knowledge of SQL at this point.

    The timestamp declaration is actually how the original column is set char(16), I think it is use more in reference than actual date value.

    Any further help would be greatly appreciated.

    Thanks!

  10. #10
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    is this what you have

    Key field1 Field2 timestamp
    record1 red sunny 1:00:00
    record2 blue cloudy 2:00:00
    record3 yellow rainy 3:00:00

    and this is what you want

    record1 field1 red 1:00:00
    record1 field2 sunny 1:00:00
    record2 field1 blue 2:00:00
    ...
    here is how to get that.

    select key, 'field1' attrib, field1 as value, timestamp
    from table
    union
    select key, 'field2' attrib, field2 value, timestamp
    from table

    not sure why you need a distinct timestamp.
    each transaction in my mind is identified the the key column(record1)

    to insert into your destination table
    insert into table2
    (key, attrib, value, timestamp)
    select key, 'field1' attrib, field1 as value, timestamp
    from table
    union
    select key, 'field2' attrib, field2 value, timestamp
    from table

    delete from table1
    where exists
    (select 'x' from table 2
    where table1.key = table2.key)

    This is off the top of my head.

    This is a set process to insert.

    concept not syntax

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •