CODEWITHSUNDEEP
sqlsql-serverdate
user2612969
user2612969

Reputation: 67

INSERT multiple data from a SINGLE SELECT Statement (SQL)

I am trying to Insert multiple data from a single select statement in SQL Server 2008.

(1) For the Select Statement:

CREATE PROCEDURE getAllDaysBetweenTwoDate
(
@StartDate DATETIME,    
@EndDate DATETIME
)
AS
BEGIN

DECLARE @TOTALCount INT
SET @StartDate = DATEADD(DAY,-1,@StartDate)
Select  @TOTALCount= DATEDIFF(DD,@StartDate,@EndDate);

WITH d AS 
        (
          SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
            OVER (ORDER BY object_id), REPLACE(@StartDate,'-',''))
          FROM sys.all_objects
        )
    SELECT AllDays From d
RETURN 
END
GO

-- With this code, I am able to get the dates between 2 dates, which is the StartDate and EndDate.

(2) I am trying to INSERT the dates into another table.

FOR EXAMPLE

@StartDate = 2013-11-05
@EndDate = 2013-11-08

The Result would be the ff:

2013-11-05,
2013-11-06,
2013-11-07,
2013-11-08

I want to insert these dates into another table.

How can I do that? Please help me.

Thanks!

Upvotes: 0

Views: 242

Answers (2)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Try:

INSERT INTO MyTable 
EXECUTE getAllDaysBetweenTwoDate @StartDate = '2013-11-05', @EndDate = '2013-11-08';

EDIT: (Insert with a '1' constant)

DECLARE @t table ([date] datetime)

INSERT INTO @t ([date])
EXECUTE getAllDaysBetweenTwoDate @StartDate = '2013-11-05', @EndDate = '2013-11-08';

INSERT INTO MyTable (DateColumn, Number1Column)
SELECT [date], '1' FROM @t

Upvotes: 3

rs.
rs.

Reputation: 27427

Based on your comments, you are trying to insert constant value along with dates. Change your code to do this

CREATE PROCEDURE getAllDaysBetweenTwoDate
(
@StartDate DATETIME,    
@EndDate DATETIME
)
AS
BEGIN

DECLARE @TOTALCount INT
SET @StartDate = DATEADD(DAY,-1,@StartDate)
Select  @TOTALCount= DATEDIFF(DD,@StartDate,@EndDate);

WITH d AS 
        (
          SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
            OVER (ORDER BY object_id), REPLACE(@StartDate,'-',''))
          FROM sys.all_objects
        )
    SELECT AllDays, 1 As Leave From d
RETURN 
END

GO

Then do this 

INSERT INTO tblAttendance2 (DateTimeIn, Leave) 
EXEC getAllDaysBetweenTwoDate 
   @StartDate = '2013-11-05', 
   @EndDate = '2013-11-08';

If you want to return user id try this

CREATE PROCEDURE getAllDaysBetweenTwoDate
(
@StartDate DATETIME,    
@EndDate DATETIME,
@userid varchar(100)
)
AS
BEGIN

DECLARE @TOTALCount INT
SET @StartDate = DATEADD(DAY,-1,@StartDate)
Select  @TOTALCount= DATEDIFF(DD,@StartDate,@EndDate);

WITH d AS 
        (
          SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER() 
            OVER (ORDER BY object_id), REPLACE(@StartDate,'-',''))
          FROM sys.all_objects
        )
    SELECT AllDays, 1 As Leave, @userid As userId From d
RETURN 
END

GO

Then do this 

INSERT INTO tblAttendance2 (DateTimeIn, Leave, UserId) 
EXEC getAllDaysBetweenTwoDate 
   @StartDate = '2013-11-05', 
   @EndDate = '2013-11-08',
   @UserId = 'xyz';

Upvotes: 1

Related Questions