November 26, 2019 at 1:45 pm
What do you mean by 'copy'? Where to? Where from? Using what tool?
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
November 26, 2019 at 1:50 pm
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 26, 2019 at 1:50 pm
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
November 26, 2019 at 1:50 pm
November 26, 2019 at 2:05 pm
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
November 26, 2019 at 3:44 pm
Use the LAG function
https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15
The OP posted readily consumable test data... show us the code to use LAG to solve this.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2019 at 4:04 pm
Use the LAG function
https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15
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
November 26, 2019 at 4:04 pm
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) "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 26, 2019 at 4:12 pm
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
November 26, 2019 at 5:58 pm
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
November 26, 2019 at 8:13 pm
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
Change is inevitable... Change for the better is not.
November 26, 2019 at 8:29 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);
Please post DDL and follow ANSI/ISO standards when asking for help.
November 26, 2019 at 8:56 pm
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
November 26, 2019 at 9:33 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply