SQL Cursor Trouble

  • Jeff Moden

    SSC Guru

    Points: 996841

    jcelko212 32090 wrote:

    We need to correct your attempt at DDL. By definition, a table must have a key, but this is impossible with your DDL because both columns are NULL You have used SQL Server to mimic a deck of punch cards!

    While usually not a problem. Technically, the path filenames have to be about 255 characters long. Why are you still using the old Sybase insert into syntax? We got rid of that  syntax decades ago. Why don't you know the proper format (ISO 8601) for a date? Is that something you inherited from incorrectly designed files in the first place?

    CREATE TABLE Filecabinet

    (source_folder_name VARCHAR(100),

    destination_folder_name VARCHAR(100),

    PRIMARY KEY (???));

    You actually had two rows that were identical (NULL, NULL) makes no sense. Here is a correction to the "current" ISO standard syntax.

    INSERT INTO Filecabinet

    VALUES

    (NULL,'02/07/2010 09:45 583680 inr.mdb'),

    (NULL,'18/11/2019 09:30 1245184 model.mdf'),

    (NULL,'18/11/2019 09:30 4194303 master.mdf'),

    (NULL,'18/11/2019 09:30 5111808 msdbdata.mdf'),

    (NULL,'19/11/2019 16:31 1060864 dhcp.mdb'),

    ('C:\aaa\ddd\',NULL),

    ('C:\abc\def\',NULL),

    ('C:\okfine\nope\',NULL);

    BWAAA-HAAAA-HAAAAA!!!! Lordy, Joe... if you're gonna preach, at least post what you preach.  You had the perfect opportunity and you blew it.

    Also, considering that VALUES didn't actually come out until 2008, your lecture about getting rid of the old syntax "decades" ago is full of hooie.  As for someones data being similar to what was on punched cards, so what?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tripleAxe

    SSCertifiable

    Points: 5605

    I'm using the ID column which got added to sort the data.

    drop table #sampledata 
    go

    create table #sampledata
    (
    id int identity(1,1),
    folder varchar(100) null,
    folder2 varchar(100) null
    )
    go

    insert into #sampledata select null,null
    insert into #sampledata select null,null
    insert into #sampledata select 'C:\okfine\nope\',null
    insert into #sampledata select null,'02/07/2010 09:45 583680 inr.mdb'
    insert into #sampledata select 'C:\abc\def\',null
    insert into #sampledata select null,'18/11/2019 09:30 4194303 master.mdf'
    insert into #sampledata select null,'18/11/2019 09:30 1245184 model.mdf'
    insert into #sampledata select null,'18/11/2019 09:30 5111808 msdbdata.mdf'
    insert into #sampledata select 'C:\aaa\ddd\',null
    insert into #sampledata select null,'19/11/2019 16:31 1060864 dhcp.mdb'
    go

    select * from #sampledata order by id;

    set nocount on;

    while exists(select * from #sampledata where id > 2 and folder is null)
    with cteFolder as
    (
    select
    id,folder, LAG(folder,1,null) over(order by id)[previous]
    from #sampledata
    )
    update #sampledata
    set folder = previous
    from cteFolder
    where cteFolder.id = #sampledata.id
    and cteFolder.previous is not null
    and #sampledata.folder is null;

    set nocount off;

    select * from #sampledata order by id;

     

    If I was doing this for real, I'd go back and get the source data differently.  Looks to me like someone has run a DIR command and pasted into Excel.

     

     

     

  • ScottPletcher

    SSC Guru

    Points: 98490

    Jeff Moden wrote:

    Also, considering that VALUES didn't actually come out until 2008, your lecture about getting rid of the old syntax "decades" ago is full of hooie.  As for someones data being similar to what was on punched cards, so what?

    How does one enter a NULL onto a punch card?  I've never professionally entered data via punched cards -- as I'm sure the OP hasn't either -- but I do know what punched cards are.  Presumably typically used for COBOL ... which didn't have a NULL value.  COBOL used LOW-VALUES or HIGH-VALUES for special-case values.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeff Moden

    SSC Guru

    Points: 996841

    To the best of my recollection (yeah, I was working with unit record equipment and computers way back then), the only "NULL" value on punch cards was to simply not punch anything in the field on the punch card.  I've pretty much forgotten everything I know about COBOL but, IIRC, you're correct... COBOL didn't have the concept of null data on a "row".  It would simply be missing a "record" (which was on a punched card, which would be missing, in this case).

    Anyway, the point I'm trying to make is that, except for such nuances as NULL, there's not much difference between punched cards and rows of data because punched cards became the rows of data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • tripleAxe

    SSCertifiable

    Points: 5605

    Using LEAD instead of LAG.....

     

     

    WITH FolderCTE AS
    (
    SELECT
    LEAD(id, 1, null) OVER (ORDER BY id) [next_folder]
    , id
    , folder
    FROM #sampledata
    WHERE folder is not null
    )
    SELECT
    sd.id
    , COALESCE(F.folder, sd.folder) [folder]
    , sd.folder2
    FROM #sampledata sd
    left outer JOIN FolderCTE F ON sd.id > F.id AND sd.id < ISNULL(F.next_folder, sd.id+1)
  • Jeff Moden

    SSC Guru

    Points: 996841

    tripleAxe wrote:

    Using LEAD instead of LAG.....

    WITH FolderCTE AS
    (
    SELECT
    LEAD(id, 1, null) OVER (ORDER BY id) [next_folder]
    , id
    , folder
    FROM #sampledata
    WHERE folder is not null
    )
    SELECT
    sd.id
    , COALESCE(F.folder, sd.folder) [folder]
    , sd.folder2
    FROM #sampledata sd
    left outer JOIN FolderCTE F ON sd.id > F.id AND sd.id < ISNULL(F.next_folder, sd.id+1)

    That relies on the "ID" column, which was not a part of the original post nor anything the OP posted later.  I've not executed the code above but it looks like it will work but nothing is going to help the OP until they get back with some amplifying information that provides a column that maintains the order, like the ID column that some folks have manufactured.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996841

    citj wrote:

    Hello, i have a large set of data and i need to copy the Path ( folder) for every line, for example first path it's for one .mdb, the second path is for next 5 rows. How can i acomplish that?

    Hey there "citj".  Do you understand that your problem has no reliable solution unless you have a column that guarantees the order of the given data?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090

    SSCrazy Eights

    Points: 9021

    >> Also, considering that VALUES didn't actually come out until 2008, your lecture about getting rid of the old syntax "decades" ago is full of hooie.<<

    Looking over my old ANSI standards, I see the values clause was always there. Originally, it was used for inserting a single row at a time; we did not get the table constructor syntax until later (I can't remember which release of the standard that was).

    The SQL–92 standard added the "default values" option, which had never actually seen used in production.

    If my arithmetic is correct, 2008 is 11 years ago, so we have two decades here and we are in the process of the second decade. Picky, picky.

    >> As for someone's data being similar to what was on punched cards, so what? <<

    A problem with thinking of row by row data processing (which is one of your manias) is that you are simulating a punch card reader by putting insert statements for each row, in the input sequence that has to be committed. When you use a table construction in the values clause, the optimizer is free to sort the rows or do anything else that wants to with them before it puts the set into the target table. Here's a chance for optimization. It does not exist with punchcard data.. I honestly don't know how much optimization SQL Server does with the insert into statement the current releases.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Phil Parkin

    SSC Guru

    Points: 244661

    jcelko212 32090 wrote:

    >> Also, considering that VALUES didn't actually come out until 2008, your lecture about getting rid of the old syntax "decades" ago is full of hooie.<<

    If my arithmetic is correct, 2008 is 11 years ago, so we have two decades here and we are in the process of the second decade. Picky, picky.

    We do IT development, where pickiness is a virtue.

    A decade is a period of 10 years. 'Decades', therefore, describes a period with a minimum term of 20 years. Thus, the only problem with Jeff's comment is that he cannot spell hooey.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply