Auto Generation Of Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto Generation Of Tables

Author

Topic

bijinn
Starting Member

1 Post

Posted - 2013-07-25 : 05:46:40

I have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? Do help as i am a beginner in SQL. I use SQL server 2008

B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-25 : 06:30:31

This is SO wrong. Just have one table, and add a MONTH column. Much easier to maintain...

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

icmmovers
Starting Member

3 Posts

Posted - 2013-07-25 : 08:07:04

quote:
Originally posted by bijinn

I have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? Do help as i am a beginner in SQL. I use SQL server 2008

B

You can also try this:

CREATE TABLE [dbo].[ticketSequence]([next_value] [numeric](4, 0) NOT NULL);

Insert initial value:

INSERT INTO [dbo].[ticketSequence]([next_value]) VALUES (1);

Do a select for update on this table each time to get the next value as follows:

DECLARE @lv_next_ticket VARCHAR(50);

UPDATE ts
SET @lv_next_ticket = 'Ticket-' + REPLICATE('0', 4 - LEN(ts.next_value)) + CONVERT(VARCHAR(4), ts.next_value),
ts.next_value = ts.next_value + 1
FROM [dbo].[ticketSequence] ts

SELECT @lv_next_ticket

unspammed

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-25 : 08:17:43

quote:
Originally posted by bijinn

I have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? Do help as i am a beginner in SQL. I use SQL server 2008

B

While you can certainly set up some job to auto generate a copy of the attendance table, it is much better to use a single table and add another column to identify the month for which any given row belings to, as Swepeso suggested.

However, don't add just a MONTH column. Either add a MONTH and a YEAR column, or add a date column (and use first of each month as the date). Adding just a month column is SO wrong because you won't be able to use it across time periods that span more than a year.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-07-25 : 09:57:01

Agree with Peso & James except you might want to just record the time/date in an appropriate column rather than separately. It all depends on what you mean by attendance. If you have a log of time in/time out then just record that and work out the details in whatever query/report you need.

The most important thing is to record at least the data you need or think you will ever need in the most appropriate way. After that, everything is relatively easy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources