How can I avoid using a cursor here?

In my client's timesheet system (Intranet-hosted ASP.NET application)
there are three tables (see below for DDL).

In normal use a client can enter data into the tblTimesheetInputDetail
directly for an Employee, identified by the Employee's PinNo.
However, they want to be able to enter timesheet information for
multiple employees. The list of these employees is in the
tblBatchTimesheetPinNos. I don't want to use a cursor to do this, but
can't work out how to do it set-wise.

Procedurally, this is what is required.

For each @PinNo in tblBatchTimesheetPinNos

INSERT INTO tblTimesheetInputDetail
(fldPinNo,
fldPayrollYear,
fldPayrollWeek,
fldPayrollDate,
fldEntryType)
SELECT
@PinNo,
fldPayrollYear,
fldPayrollWeek,
fldPayrollDate,
fldEntryType
FROM
tblBatchTimesheetInputDetail
Next @PinNo

Any thoughts on a better method?

Thanks

Edward

CREATE TABLE [dbo].[tblTimesheetInputDetail] (
[fldID] [int] IDENTITY (1, 1) NOT NULL ,
[fldPinNo] [int] NULL ,
[fldPayrollYear] [smallint] NULL ,
[fldPayrollWeek] [smallint] NULL ,
[fldPayrollDate] [datetime] NULL ,
[fldEntryType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblBatchTimesheetInputDetail] (
[fldID] [int] IDENTITY (1, 1) NOT NULL ,
[fldPayrollYear] [smallint] NULL ,
[fldPayrollWeek] [smallint] NULL ,
[fldPayrollDate] [datetime] NULL ,
[fldEntryType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[tblBatchTimesheetPinNos] (
[fldPinNo] [int] NOT NULL
) ON [PRIMARY]
GO

Re: How can I avoid using a cursor here?

> Any thoughts on a better method?

You can use a CROSS JOIN to insert a row per PinNo as a set. For example:

INSERT INTO tblTimesheetInputDetail
(
fldPinNo,
fldPayrollYear,
fldPayrollWeek,
fldPayrollDate,
fldEntryType
)
SELECT
tblBatchTimesheetInputDetail.fldPinNo,
fldPayrollYear,
fldPayrollWeek,
fldPayrollDate,
fldEntryType
FROM dbo.tblBatchTimesheetInputDetail.fldPinNo
CROSS JOIN dbo.tblBatchTimesheetPinNos


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

wrote in message
news:20bcf644-0b9a-47b4-b891-328716ca06f7@t54g2000hsg.google groups.com...
> In my client's timesheet system (Intranet-hosted ASP.NET application)
> there are three tables (see below for DDL).
>
> In normal use a client can enter data into the tblTimesheetInputDetail
> directly for an Employee, identified by the Employee's PinNo.
> However, they want to be able to enter timesheet information for
> multiple employees. The list of these employees is in the
> tblBatchTimesheetPinNos. I don't want to use a cursor to do this, but
> can't work out how to do it set-wise.
>
> Procedurally, this is what is required.
>
> For each @PinNo in tblBatchTimesheetPinNos
>
> INSERT INTO tblTimesheetInputDetail
> (fldPinNo,
> fldPayrollYear,
> fldPayrollWeek,
> fldPayrollDate,
> fldEntryType)
> SELECT
> @PinNo,
> fldPayrollYear,
> fldPayrollWeek,
> fldPayrollDate,
> fldEntryType
> FROM
> tblBatchTimesheetInputDetail
> Next @PinNo
>
> Any thoughts on a better method?
>
> Thanks
>
> Edward
>
> CREATE TABLE [dbo].[tblTimesheetInputDetail] (
> [fldID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldPinNo] [int] NULL ,
> [fldPayrollYear] [smallint] NULL ,
> [fldPayrollWeek] [smallint] NULL ,
> [fldPayrollDate] [datetime] NULL ,
> [fldEntryType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblBatchTimesheetInputDetail] (
> [fldID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldPayrollYear] [smallint] NULL ,
> [fldPayrollWeek] [smallint] NULL ,
> [fldPayrollDate] [datetime] NULL ,
> [fldEntryType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> CREATE TABLE [dbo].[tblBatchTimesheetPinNos] (
> [fldPinNo] [int] NOT NULL
> ) ON [PRIMARY]
> GO

Re: How can I avoid using a cursor here?

On 9 Apr, 13:08, "Dan Guzman"
wrote:
> > Any thoughts on a better method?
>
> You can use a CROSS JOIN to insert a row per PinNo as a set. =A0For exampl=
e:
>
> INSERT INTO tblTimesheetInputDetail
> =A0 =A0 (
> =A0 =A0 fldPinNo,
> =A0 =A0 fldPayrollYear,
> =A0 =A0 fldPayrollWeek,
> =A0 =A0 fldPayrollDate,
> =A0 =A0 fldEntryType
> )
> SELECT
> =A0 =A0 tblBatchTimesheetInputDetail.fldPinNo,
> =A0 =A0 fldPayrollYear,
> =A0 =A0 fldPayrollWeek,
> =A0 =A0 fldPayrollDate,
> =A0 =A0 fldEntryType
> FROM dbo.tblBatchTimesheetInputDetail.fldPinNo
> CROSS JOIN dbo.tblBatchTimesheetPinNos
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/

Superb - thanks very much.

Edward

Re: How can I avoid using a cursor here?

>> In normal use a client can enter data into the TimesheetInputDetail directly for an Employee, identified by the Employee's pin_nbr.
However, they want to be able to enter time sheet information for
multiple employees. The list of these employees is in the
BatchTimesheetPinNos. I don't want to use a cursor to do this, but
can't work out how to do it set-wise. <<

If you want to use a set-based approach, you might start with a
properly normalized schema. This thing actually uses "tbl-" and "fld-"
prefixes as if you were still writing code for a 1960's version of
BASIC. Oh, a field is nothing like a relational column, so the "fld-"
is a really strong symptom of bad SQL programming. You have computed
data in columns. You have no keys -- IDENTITY is completely wrong.
Everything is NULL-able. You have no DRI and have defined things so
as to make data integrity impossible.

CREATE TABLE BatchTimesheetDetails
(pin_nbr INTEGER NOT NULL
REFERENCES BatchTimesheetPinNbrs (pin_nbr),
payroll_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
entry_type CHAR(1) DEFAULT '?' NOT NULL,
PRIMARY KEY (pin_nbr, payroll_date)); -- or PRIMARY KEY (pin_nbr,
payroll_date, entry_type) ?

Get the week and year from the payroll date instead of keeping
redundant, and probably erroneous, copies of that computable data.
Put it in a VIEW, so you know it is right.

CREATE TABLE BatchTimesheetPinNbrs
(pin_nbr INTEGER NOT NULL PRIMARY key);

The insertions are now a simple cross join that will load DEFAULT
values into the base table.

INSERT INTO BatchTimesheetDetails (pin_nbr, payroll_date, entry_type)
SELECT P.pin_nbr, D.payroll_date, D.entry_type
FROM BatchTimesheetDetails AS D
CROSS JOIN
BatchTimesheetPinNbrs AS P;

This can be made into a stored procedure that takes parameters for the
payroll_date and entry_type.