Insert multiple records for a single ID

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

SQL Server 2005 Forums

Transact-SQL (2005)

Insert multiple records for a single ID

Author

Topic

headbuzz
Starting Member

11 Posts

Posted - 2010-03-03 : 11:13:39

My project has a 'userid' as an identifier which needs to take a 'fromdate' and 'todate' many times to come up with a count. For example, if the user id is say '1', I need to be able to display like this:

User Id:1

1. Fromdate:_____ ToDate:___
2. Fromdate:_____ ToDate:___
3. Fromdate:_____ ToDate:___

and finally, Count used: sums of (Todate-fromdate) off all 3 above

User Id:2
1. Fromdate:_____ ToDate:___
2. Fromdate:_____ ToDate:___
and finally, Count used : sums of (Todate-fromdate) off all 2 above

UserId:3
1. Fromdate:_____ ToDate:___
2. Fromdate:_____ ToDate:___
3. Fromdate:_____ ToDate:___
4. Fromdate:_____ ToDate:___

and finally Count used:sums of (Todate-fromdate) off all 4 above

and so on..

I have tried inserting using a regular insert statement but it would only work for the last 'fromdate' and 'todate' values. How do I insert multiple records for the single userid and yet not lose the old data?

Any help would be really appreciated!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:16:20

whats the basis for number of records to be inserted for a particular user? how do you determine what all date valued records need to be inserted for each user?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

headbuzz
Starting Member

11 Posts

Posted - 2010-03-03 : 11:19:48

The 'todate' and 'fromdate' are basically inputs from users from a aspx page. I want to take those inputs and store them in a database and display them in the order shown above whenever the user needs it. These dates are very random depending on what the user wants to enter.

headbuzz
Starting Member

11 Posts

Posted - 2010-03-03 : 11:25:47

This query I showed just can store the last entered values if 'todate' and 'fromdate'. How do I instead store multiple values instead of just one for each userid?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:32:44

what? does that mean you add same set of values multiple times for each user?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

headbuzz
Starting Member

11 Posts

Posted - 2010-03-03 : 11:42:35

It basically doesn't take the same multiple values.

I'm actually comping up with an employee management system. I need to save to the user_profile all the days an employee took the days off. They can take the days off whenever they want to. This, as you know, could be very random.

Later when an employee wants to apply for a vacation, he has to see how many he has already used up. For that I need to display all the vacation days he/she used that were stored in the DB.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-03 : 11:58:41

so the dates should be stored somewhere right? may be a leave table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

headbuzz
Starting Member

11 Posts

Posted - 2010-03-03 : 12:10:41

Yes, that's exactly right.

But my table is already set up for some other functionalities that are needed in the project. So I thought, I need to come up with a new table since it is only for store and display purposes by relating the userid of this new table to userid of the other table that I'm using..if I'm not missing out something.

I can provide you with the other table's queries that I'm using if it's needed. If I can proceed with a new table, that would really be great!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 09:39:36

if its for display purpose wont a view be enough?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources