根据开始和结束日期插入多行到SQL Server [英] Insert multiple rows into SQL Server based on start and end dates

问题描述

我需要根据若干行插入到一个SQL Server数据库开始日期和结束日期文本框。

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

例如。 tbStartDate.Text =25/12/2012和 tbEndDate.Text =29/12/2012因此，我需要插入各行的日期如下：

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

请你能帮助我必要的T-SQL来实现这一目标？

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

推荐答案

由于总是有几个方面。下面是其中一些：

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

1. 您可以写在你的应用程序code，通过天循环并插入每天一条记录。 （一般最差设计）

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

您可以调用一些SQL脚本做这一切在数据库中。

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

您可以在存储过程中包裹你的SQL脚本，并通过在开始和结束日期，并获得存储过程来为你做它。

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.

您可以交叉连接到pre现有理货表，并用它来生成你的记录。

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

如果您可以提供

的SQL Server版本-the您使用

-the version of SQL Server that you're using

- 什么表看起来像

-what the table looks like

- 不论你使用C＃或VB

-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.

反正这里是选项3适合你。

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

然后，你需要从ASP.Net调用这个存储过程（称为dbo.t_test），并在两个日期指标的影响传递的日期。

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

这篇关于根据开始和结束日期插入多行到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！