Insert multiple records for a single ID

Question

Insert multiple records for a single ID

harshalnag

SSC Enthusiast

Points: 110
More actions
March 3, 2010 at 8:15 am

#211798

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

aaron.reese SSChampion Points: 13498 More actions · Answer 1

Sorry, I'm not sure I understand what is being asked for

Is the question about how to get the records into the table or about how to get a sum for each user of the total days entered?

If it is the first one, How do you know how many records you will need to insert for each user? In the examples given, each user has a different number of records.

Obiron

harshalnag SSC Enthusiast Points: 110 More actions · Answer 2

Yes it's the first one..and the number IS the problem..it is going to be very random.

This is actually a management system for employees where the employees enter the days they have taken off as vacation. It should be taken as "fromdate" and "todate". Whenever a user wants to check, he should be able to see all the fromdates and todates he has used so far so that he can decide further.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Instead of that verbal description would you mind setting up some table definition (target table), input values that are provided and expected result set in your target table.

If you'd like to get a solution on how to query that table, post those input values together with your expected result as well.

For a fast and usually coded and tested answer please read and follow the first link in my signature on how to post sample data.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

would something like this help?

if i know you are going to take 12/21/2010 off thru 12/25/2010 for the holidays, you want to generate teh 5 days of records?

--results:

myuserID N

----------- ----------------------- -----------------------

1 2010-12-21 00:00:00.000 2010-12-22 00:00:00.000

1 2010-12-22 00:00:00.000 2010-12-23 00:00:00.000

1 2010-12-23 00:00:00.000 2010-12-24 00:00:00.000

1 2010-12-24 00:00:00.000 2010-12-25 00:00:00.000

1 2010-12-25 00:00:00.000 2010-12-26 00:00:00.000

and the code for getting it

--ten years before and after todays date:

with TallyCalendar as (

SELECT dateadd( dd,-3650 + RW ,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)) As N

FROM (

SELECT TOP 7300

row_number() OVER (ORDER BY sc1.id) AS RW

FROM Master.dbo.SysColumns sc1

CROSS JOIN Master.dbo.SysColumns sc2

) X

)

select 1 as myuserID,TallyCalendar.N,TallyCalendar.N + 1

from TallyCalendar

where TallyCalendar.N between '12/21/2010' and '12/25/2010'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

harshalnag SSC Enthusiast Points: 110 More actions · Answer 5

What you said is exactly right when you know the dates beforehand.

How can I do a similar thing if I don't have the dates with me (The employees can choose a holiday when they want to, isn't it?). All I can give to a employee is a form like this:

Todate:

Fromdate:

"Submit" button.

The employee has to just enter the dates and click submit. since there could be many employees, I need to link each todate and fromdate to that userid. Also since there could be multiple vacations, there could be multiple "todates" and "fromdates". So it means that each employee has multiple holidays and all those need to be displayed when needed.

Doing it multiple times and storing in the database is the issue.

Thanks!

harshalnag SSC Enthusiast Points: 110 More actions · Answer 6

This is actually very similar to a customer-order management system (one-to-many relationship) used on websites like amazon or bestbuy.

Each user can buy what he wants to and check out. Later he can come back and look at the cart and maybe do some modifications or cancel the order.

My requirements are much similar-instead of customer I have am employee with a 'userid'.

Instead of orders-employee has "todate" and "fromdate" which could be many in number.

I need to get each user's holidays using a form and store them.

The user can go back and verify it when he wants to.

Hope this clears the confusion.

aaron.reese SSChampion Points: 13498 More actions · Answer 7

I have a couple of observations from the postings you have already made.

1)Is this question more about the form than the database...

E.g.

I am calling up my holiday list and am presented with 5 previously booked holidays. Do you want the user to be able to delete one, edit one and create a new one. If so then this is probably the wrong forum although people here will be able to help. If so, what is your user interface written in (PHP/ASP/C#/,net/Access etc...)

2) in your example postings you had five consecutive days entered as five records. WHY. With a from and a to date, why can I not enter From 2009-12-20 to 2009-12-25 into a single record. If it is an issue of calcuating weekends and bank holidays there are a hundred scripts out there to do those calcuations. If you want each day's holiday to be entered separately then why have from and to unless they are to cater for half days, in which case why not have a field for AM/PM/NULL (i.e all day)

harshalnag SSC Enthusiast Points: 110 More actions · Answer 8

It's definitely not about forms. I already know how the form would look like and how the connections are set up to databases.

I need to come up with a SQL query like Lowell did which would help me to insert fromdates and todates for each userid. But I just want to know if I can just use a single Data table or do I need to use multiple tables and how do I need to link those up.

And the 2nd part-the management system needs to take a 'whole day' as a input. Even if the user needs to change/edit something, he cannot do it. Hence I said "display". So please do not get confused about this.

All I need is a single insert query to use.

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 9

harshalnag (3/3/2010)
This is actually very similar to a customer-order management system (one-to-many relationship) used on websites like amazon or bestbuy.
Each user can buy what he wants to and check out. Later he can come back and look at the cart and maybe do some modifications or cancel the order.
My requirements are much similar-instead of customer I have am employee with a 'userid'.
Instead of orders-employee has "todate" and "fromdate" which could be many in number.
I need to get each user's holidays using a form and store them.
The user can go back and verify it when he wants to.
Hope this clears the confusion.

Perhaps I'm over simplifying but why don't you just do one insert per "submit"? All into the same table, of course.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aaron.reese SSChampion Points: 13498 More actions · Answer 10

My thoughts exactly, which is why I thought the question may be asking for something more complicated.

You will need a single insert statement for each record that you want to write into the database.

Insert will work with the syntax

Insert into mytable (col1,col2,col3...) values (value1,value2,value3)

or

insert into mytable (col1,col2,col3) select value1,value2,value3 from someOtherTable

you could set your select statement up as a union statement but you would effectively be doing multiple inserts.

The only complication I can think of is if you need to make sure that ALL inserts work or no inserts get made, you will need to start a transaction and then comitt it at the end of the inserts.