# 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\',nullinsert into #sampledata select null,'19/11/2019 16:31 1060864 dhcp.mdb'go select * from #sampledatago

• 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?"

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

• 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 #sampledataGO;WITH dirs AS (    SELECT id, folder    FROM #sampledata    WHERE folder IS NOT NULL)SELECT d.folder, sd.folder2FROM #sampledata sdOUTER APPLY (    SELECT TOP (1) d.folder    FROM dirs d    WHERE d.id < sd.id     ORDER BY d.id DESC) AS dWHERE 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 fWHERE f.folder2 IS NOT NULL

Drew

J. Drew Allen

• 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?"

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

• 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

• 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?"