SQL Cursor Trouble

  • citj

    SSC Enthusiast

    Points: 184

    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?

    Attachments:
    You must be logged in to view attached files.
  • Phil Parkin

    SSC Guru

    Points: 244650

    What do you mean by 'copy'? Where to? Where from? Using what tool?

    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.

  • Neil Burton

    SSC-Insane

    Points: 22225

    Hello and welcome to the forum.

    You almost certainly don't need a cursor for this but to get the best help we'll need a bit from you.  It's much easier to provide a solution if we've got tables and test data to use.  If you read the link in my signature, it'll show you how to set up something with which we can work.

     

     


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • citj

    SSC Enthusiast

    Points: 184

    sorry for lack of explanation, sql server

     

    drop table #sampledata 
    go

    create table #sampledata
    (
    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
    go

    • This reply was modified 8 months, 1 week ago by  citj.
    Attachments:
    You must be logged in to view attached files.
  • tripleAxe

    SSCertifiable

    Points: 5605

  • Neil Burton

    SSC-Insane

    Points: 22225

    OK, we're getting somewhere now.

    The big question is how to you unambiguously tie folder to folder2.  I can see what you want to do but there's no way to guarantee your results without something to link the two columns together.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Jeff Moden

    SSC Guru

    Points: 996810

    The OP posted readily consumable test data... show us the code to use LAG to solve this.

    --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)

  • drew.allen

    SSC Guru

    Points: 76739

    LAG() is not going to work here, because it goes back a specific number of records and this solution requires going back an arbitrary number of records.

    No solution is going to work here, because all solutions require a sort and there was no field provided that would ensure the correct sort.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • ScottPletcher

    SSC Guru

    Points: 98475

    DROP TABLE #sampledata 
    GO

    CREATE TABLE #sampledata
    (
    id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, --<<--!!
    folder varchar(100) NULL,
    folder2 varchar(100) NULL
    )
    GO
    INSERT INTO #sampledata VALUES
    (NULL,NULL),
    (NULL,NULL),
    ('C:\okfine\nope\',NULL),
    (NULL,'02/07/2010 09:45 583680 inr.mdb'),
    ('C:\abc\def\',NULL),
    (NULL,'18/11/2019 09:30 4194303 master.mdf'),
    (NULL,'18/11/2019 09:30 1245184 model.mdf'),
    (NULL,'18/11/2019 09:30 5111808 msdbdata.mdf'),
    ('C:\aaa\ddd\',NULL),
    (NULL,'19/11/2019 16:31 1060864 dhcp.mdb')
    GO
    --SELECT * FROM #sampledata
    GO
    ;WITH dirs AS (
    SELECT id, folder
    FROM #sampledata
    WHERE folder IS NOT NULL
    )
    SELECT d.folder, sd.folder2
    FROM #sampledata sd
    OUTER APPLY (
    SELECT TOP (1) d.folder
    FROM dirs d
    WHERE d.id < sd.id
    ORDER BY d.id DESC
    ) AS d
    WHERE sd.folder IS NULL AND sd.folder2 IS NOT NULL

    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."

  • Jonathan AC Roberts

    SSCoach

    Points: 17316

    This gives the results you have requested. But you can't guarantee these results if you have nothing concrete to order the data by.

    ;WITH CTE AS 
    (
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowNum
    FROM #sampledata
    )
    SELECT ISNULL(b.folder,a.folder) folder,
    a.folder2
    FROM CTE a
    OUTER APPLY(SELECT TOP(1) b.folder
    FROM CTE b
    WHERE b.RowNum < a.RowNum
    AND b.folder IS NOT NULL
    ORDER BY b.RowNum DESC) b

     

     

  • drew.allen

    SSC Guru

    Points: 76739

    The following requires fewer scans, so it will perform better.  It does depend on having the identity field with a primary key.  You can use CHAR rather than BINARY as the intermediate data type, but that can cause issues when converting numbers.

    ; WITH folders AS
    (
    SELECT
    s.id
    , CAST(SUBSTRING(MAX(CAST(ID AS BINARY(5)) + CAST(folder AS BINARY(100))) OVER(ORDER BY s.id ROWS UNBOUNDED PRECEDING), 6, 100) AS VARCHAR(100)) AS folder
    , s.folder2
    FROM #sampledata AS s
    )
    SELECT *
    FROM folders AS f
    WHERE f.folder2 IS NOT NULL

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeff Moden

    SSC Guru

    Points: 996810

    drew.allen wrote:

    No solution is going to work here, because all solutions require a sort and there was no field provided that would ensure the correct sort.

    This was and still is the only correct answer according to the data provided (which is why I asked TripleAxe to post his solution).  What changed your mind?

    --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

    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);

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

  • drew.allen

    SSC Guru

    Points: 76739

    Jeff Moden wrote:

    drew.allen wrote:

    No solution is going to work here, because all solutions require a sort and there was no field provided that would ensure the correct sort.

    This was and still is the only correct answer according to the data provided (which is why I asked TripleAxe to post his solution).  What changed your mind?

    Scott posted a table definition with an identity that could be used for the sort and that is probably the simplest way to resolve the missing sort field.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeff Moden

    SSC Guru

    Points: 996810

    drew.allen wrote:

    Jeff Moden wrote:

    drew.allen wrote:

    No solution is going to work here, because all solutions require a sort and there was no field provided that would ensure the correct sort.

    This was and still is the only correct answer according to the data provided (which is why I asked TripleAxe to post his solution).  What changed your mind?

    Scott posted a table definition with an identity that could be used for the sort and that is probably the simplest way to resolve the missing sort field.

    Drew

    Understood and thanks for the feedback.  I hope the OP understands that there absolutely must be something to adequately preserve the order of the table and that the stuff he posted doesn't have it.

     

    --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)

Viewing 15 posts - 1 through 15 (of 24 total)

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