November 26, 2019 at 9:39 pm
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
Change is inevitable... Change for the better is not.
November 27, 2019 at 9:13 am
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.
November 27, 2019 at 3:11 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 27, 2019 at 3:16 pm
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
Change is inevitable... Change for the better is not.
November 28, 2019 at 10:03 am
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)
November 28, 2019 at 10:11 pm
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
Change is inevitable... Change for the better is not.
November 28, 2019 at 10:12 pm
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
Change is inevitable... Change for the better is not.
November 29, 2019 at 4:59 pm
>> 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.
November 29, 2019 at 5:31 pm
>> 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply