June 7, 2009 at 5:51 am
I have a table with columns as
empno, from date, to date, project with the data in it as
1, 1-oct-2009, 4-Oct-2009, abc
i want to insert the data into a table with columns as
empno, date, project and the data like
1,1-Oct-2009, abc,
1,2-Oct-2009, abc
1,3-Oct-2009, abc
1,4-Oct-2009, abc
Please help me as to how to achieve this!!
Pramod
June 7, 2009 at 8:46 am
You can use a Tally table for this:
DECLARE @t TABLE (EmpNo INT, FromDate DATETIME, ToDate DATETIME, Project VARCHAR(30))
INSERT INTO @t
SELECT 1, '2009-10-01', '2009-10-04', 'abc'
SELECT
EmpNo,
t2.Dt,
Project
FROM @t t
CROSS APPLY
(
SELECT
DATEADD(DAY, N - 1, FromDate) Dt
FROM Tally
WHERE N <= DATEDIFF(DAY, FromDate, ToDate) + 1
) t2
If you don't know a Tally table search this site. You will find an article by Jeff Moden which describes very well.
Flo
June 7, 2009 at 8:57 am
If the dates are sequential you could use this
-- these would be your input parameters
DECLARE
@empno int,
@start_date datetime,
@end_date datetime,
@project nvarchar(50)
-- set them to some values for testing
SELECT
@empno = 1,
@start_date = '10/01/2009',
@end_date = '10/04/2009',
@project = 'abc'
-- this is used by the loop
DECLARE @working_date datetime
SET @working_date = @start_date
WHILE @working_date < @end_date
BEGIN
INSERT
MyTable
(
empno,
[date],
project
)
SELECT
@empno,
@working_date,
@project
SET @working_date = DATEADD(d, 1, @working_date)
END
June 7, 2009 at 9:04 am
WILLIAM MITCHELL (6/7/2009)
If the dates are sequential you could use this
-- these would be your input parameters
DECLARE
@empno int,
@start_date datetime,
@end_date datetime,
@project nvarchar(50)
-- set them to some values for testing
SELECT
@empno = 1,
@start_date = '10/01/2009',
@end_date = '10/04/2009',
@project = 'abc'
-- this is used by the loop
DECLARE @working_date datetime
SET @working_date = @start_date
WHILE @working_date < @end_date
BEGIN
INSERT
MyTable
(
empno,
[date],
project
)
SELECT
@empno,
@working_date,
@project
SET @working_date = DATEADD(d, 1, @working_date)
END
Hi William
I think a RBAR solution runs into performance problems if there are more than one project or more four days.
Flo
June 7, 2009 at 9:12 am
Sorry, did not read the entire question, missed the part where the dates come from another table.
June 7, 2009 at 9:14 am
WILLIAM MITCHELL (6/7/2009)
Sorry, did not read the entire question, missed the part where the dates come from another table.
Nothing to apologize :-). I just intended to ensure that the OP knows the reason of set-based solutions.
Flo
June 7, 2009 at 9:18 am
Florian Reischl (6/7/2009)
WILLIAM MITCHELL (6/7/2009)
Sorry, did not read the entire question, missed the part where the dates come from another table.Nothing to apologize :-). I just intended to ensure that the OP knows the reason of set-based solutions.
Flo
Florian,
Your code you posted requires the Tally table to exist, can you post the code for that too?
June 7, 2009 at 9:26 am
The script is quite simple:
SELECT TOP(11000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
INTO Tally
FROM master.sys.all_columns c1
CROSS JOIN master.sys.all_columns c2
ALTER TABLE Tally
ALTER COLUMN N INT NOT NULL
ALTER TABLE Tally
ADD CONSTRAINT PK_Tally
PRIMARY KEY CLUSTERED
(N)
WITH ( FILLFACTOR = 100 )
More important
Know how it works. As I wrote above there is a great article which describes the tally table and its features:
http://www.sqlservercentral.com/articles/T-SQL/62867/
Flo
June 7, 2009 at 10:07 am
That Tally table thing is pretty slick. In my case it created 11000 rows. I will definitely study how it works.
Anyway, here is a possible final solution that inserts from the source table & also prevents duplicates in the destination:
-- one-time setup for source & destination tables
CREATE TABLE tbl_Source
(
EmpNo INT,
FromDate DATETIME,
ToDate DATETIME,
Project VARCHAR(30)
)
CREATE TABLE tbl_Destination
(
EmpNo INT,
TheDate DATETIME,
Project VARCHAR(30)
)
INSERT tbl_Source ( EmpNo, FromDate, ToDate, Project )
SELECT 1, '1-oct-2009', '4-Oct-2009', 'abc'
INSERT tbl_Source ( EmpNo, FromDate, ToDate, Project )
SELECT 2, '1-oct-2009', '4-Oct-2009', 'def'
INSERT tbl_Source ( EmpNo, FromDate, ToDate, Project )
SELECT 2, '5-oct-2009', '12-Oct-2009', 'ghi'
-- end of one-time table setup
INSERT tbl_Destination
(
EmpNo,
TheDate,
Project
)
SELECT
EmpNo,
t2.Dt,
Project
FROM
tbl_Source src
CROSS APPLY
(
SELECT
DATEADD(DAY, N - 1, FromDate) Dt
FROM
Tally
WHERE
N <= DATEDIFF(DAY, FromDate, ToDate) + 1
) t2
WHERE NOT EXISTS
(
SELECT 1 FROM tbl_Destination
WHERE EmpNo = src.EmpNo
AND TheDate = t2.Dt
AND Project = src.Project
)
June 7, 2009 at 3:26 pm
Well done one and all, but there is no need for such complex solutions.
INSERT INTO dbo.tbl_Destination
(EmpNo, TheDate, Project)
SELECT s.EmpNo, DATEADD(dd, t.n, s.FromDate) AS TheDate, s.Project
FROM dbo.tbl_Source s
CROSS JOIN dbo.Tally t
WHERE t.n <= DATEDIFF(dd,s.FromDate, s.ToDate) +1
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2009 at 12:02 am
Thanks a lot Guys... it was really helpful.
June 8, 2009 at 4:35 pm
pramod_yg (6/8/2009)
Thanks a lot Guys... it was really helpful.
Thank YOU for the feedback. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply