SQL Cursor Trouble

Question

SQL Cursor Trouble

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

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

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.

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

tripleAxe SSCertifiable Points: 5706 More actions · Answer 2

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: 100952 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

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.

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

tripleAxe SSCertifiable Points: 5706 More actions · Answer 5

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: 1003894 More actions · Answer 6

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.

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

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.

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

jcelko212 32090 SSCrazy Eights Points: 9295 More actions · Answer 8

>> 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: 246723 More actions · Answer 9

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.