Insert multiple rows into SQL Server based on star

问题:

I need to insert several rows into a SQL Server database based on Start Date and End Date textboxes.

E.G. tbStartDate.Text = "25/12/2012" and tbEndDate.Text = "29/12/2012" therefore I need to insert individual rows for the following dates:

25/12/2012
26/12/2012
27/12/2012
28/12/2012
29/12/2012

Please can you help me with the necessary T-SQL to achieve this?

回答1:

As always there are a few ways. Here are some of them:

1. You can write code in your app that loops through the days and inserts a single record per day. (generally the worst design)

2. You can call some SQL script to do it all in the database.

3. You can wrap up your SQL script in a stored procedure and pass in the start and end date and get the stored procedure to do it for you.

4. You can cross join to a pre existing tally table and use it to generate your records.

If you can provide

-the version of SQL Server that you're using

-what the table looks like

-whether you're using C# or VB

then we can help further as it can be difficult to pass dates into databases. It can be particularly difficult if you do not validate them.

Anyway here is option 3 for you.

CREATE PROC dbo.t_test 
@StartDate DATETIME,
@EndDate DATETIME
AS

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO YourTable(YourDateField) VALUES (@StartDate)
    SET @StartDate = DATEADD(d,1,@StartDate)
END

Then you need to call this stored procedure (called dbo.t_test) from ASP.Net and pass in your two date parametes as dates.

回答2:

Declare @Startdate datetime
Select @Startdate='20121025'
While @Startdate<='20121029'
    begin
      if not exists(select * from dummy where DATE=@Startdate)
        insert into dummy (date) values (@Startdate)
        set @Startdate=@Startdate + 1
    end;