insert records - 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

insert records

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-31 : 13:16:47

I have a query:

SELECT
hdr.Date, hdr.TicketNumber, hdr.HazardPay,
pay.EmployeeNumber, pay.PayCategory,
pay.TravelTime + pay.WorkTimeOnJob + pay.CoTime + pay.Hours AS Hours,
pay.ID
FROM dbo.WorkTicketHdr AS hdr INNER JOIN
dbo.PayrollDetailTable AS pay ON hdr.TicketNumber = pay.TicketNumber
WHERE hdr.TicketNumber = 'testtom'

Output:

Date TicketNumber HazardPay EmployeeNumber PayCategory Hours ID
2010-03-30 testtom 1 101485 OPRTR 4 78968
2010-03-30 testtom 1 233129 DERIC 4 79295

So basically for each Ticket there can be multiple employees working on it. I would like to insert a row or record for each employee on a Ticket where HazardPay=1. The PayCategory for these inserted records would be 'HAZ'. The Date would be the same, the TicketNumber would be the same, etc. The ID is the PK and also Identity. I haven't done this before and could use a kick in the right direction. Any help would be appreciated. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:19:49

[code]insert into table (Date, TicketNumber, HazardPay, EmployeeNumber, PayCategory, Hours)
SELECT Date, TicketNumber, HazardPay, EmployeeNumber, 'HAZ', Hours
FROM Table
WHERE HazardPay=1
[/code]

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

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-31 : 13:39:50

OK. Stupid question. I have a Access front-end where this data is entered. A main Ticket form with an Employee Pay sub-form. Is there a way that when a check box 'Hazard Pay' is checked that the insert that you gave me can be automatically done on the back-end sql server or am I going to have to do this on the Access front-end? If on the front-end can I use this sql in a Run Sql code? Thanks for your help.

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-31 : 13:45:12

Also on the script that you replied with....

is this specific to the Ticket Number or if I run it with let's say 100 Tickets in the table will it all update with the same information? Not sure how I understand how this is distinct on the Ticket Number? Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:48:41

you need make this into a procedure and call it from access front end while clicking on your check box

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

osupratt
Posting Yak Master

238 Posts

Posted - 2010-03-31 : 15:39:41

how might this be done. create SP on back-end i have done. calling a SP from Access I have not. can you show an example possibly? thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:13:04

I'm not an access expert. post in access forum to get help on this

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

osupratt
Posting Yak Master

238 Posts

Posted - 2010-04-02 : 12:57:04

OK. My thinking might be a bit off. When I run the query below it inserts correctly and everything is fine. The issue is that if I have 3 'tickets' that I enter and then run the insert query it'll insert right, but if I run it again it inserts it again. If I run it again, it'll insert again. And over and over.

How do I limit it to only insert or add records to the Ticket that that is currently being added or entered? I hope this makes sense. You can imagine if I had this query run everytime a check box is selected that the table will be full of duplicated records.

Thanks for any help you may provide.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 13:03:09

do you mean this?


insert into table (Date,	TicketNumber,	HazardPay,	EmployeeNumber,	PayCategory,	Hours)
SELECT Date,	TicketNumber,	HazardPay,	EmployeeNumber,	'HAZ',	Hours
FROM Table
WHERE HazardPay=1
AND NOT EXISTS(SELECT 1 FROM Table WHERE Date= t.Date AND TicketNumber = t.TicketNumber AND HazardPay=t.HazardPay AND EmployeeNumber=t.EmployeeNumber	 AND PayCategory='HAZ'AND Hours = t.Hours AND HazardPay=1)

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

osupratt
Posting Yak Master

238 Posts

Posted - 2010-04-02 : 13:37:03

The query below still adds records even though the records have already been added. So if I run it 3 times it adds the records 3 times. Did I do something wrong?

insert into dbo.PayrollDetailTable (pay.Date,pay.DailyWorkTicketNumber,
pay.EmployeeNumber,pay.PayCategory,pay.Hours)
SELECT pay.Date,pay.DailyWorkTicketNumber,pay.EmployeeNumber,'HAZ',pay.Hours
FROM dbo.MercerDailyWorkTicketHdr AS hdr INNER JOIN
dbo.PayrollDetailTable AS pay ON hdr.DailyWorkTicketNumber = pay.DailyWorkTicketNumber
WHERE hdr.HazardPay=1 and pay.DailyWorkTicketNumber='testtom'
AND NOT EXISTS (SELECT 1 FROM dbo.PayrollDetailTable WHERE pay.Date= Date
AND pay.DailyWorkTicketNumber = DailyWorkTicketNumber AND HazardPay=HazardPay
AND pay.EmployeeNumber=EmployeeNumber AND pay.PayCategory='HAZ'AND pay.Hours = Hours AND HazardPay=1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 13:44:52

quote:
Originally posted by osupratt

The query below still adds records even though the records have already been added. So if I run it 3 times it adds the records 3 times. Did I do something wrong?

insert into dbo.PayrollDetailTable (pay.Date,pay.DailyWorkTicketNumber,
pay.EmployeeNumber,pay.PayCategory,pay.Hours)
SELECT pay.Date,pay.DailyWorkTicketNumber,pay.EmployeeNumber,'HAZ',pay.Hours
FROM dbo.MercerDailyWorkTicketHdr AS hdr INNER JOIN
dbo.PayrollDetailTable AS pay ON hdr.DailyWorkTicketNumber = pay.DailyWorkTicketNumber
WHERE hdr.HazardPay=1 and pay.DailyWorkTicketNumber='testtom'
AND NOT EXISTS (SELECT 1 FROM dbo.PayrollDetailTable WHERE pay.Date= Date
AND pay.DailyWorkTicketNumber = DailyWorkTicketNumber AND hdr.HazardPay=HazardPay
AND pay.EmployeeNumber=EmployeeNumber AND pay.PayCategory='HAZ'AND pay.Hours = Hours AND HazardPay=1)

small modification above

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

osupratt
Posting Yak Master

238 Posts

Posted - 2010-04-02 : 13:44:55

i see what i did. thanks for your help again! i appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 13:45:08

welcome

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

byrdzeye
Starting Member

14 Posts

Posted - 2010-04-02 : 13:53:13

Calling an SP from Access.

Create a query and set its type to Pass-through and set the connection string.
You end up with a white box that enter SQL Server syntax text in.
To run a SP, its as easy as entering just the sproc name if no parms required. Thats it.
Besides executing much faster, you HAVE to use SQL Server syntax and folks here will be more than glad to help with your queries.
You prolly need to your other Access issues addressed elsewhere. Can't help you here methinks.

osupratt
Posting Yak Master

238 Posts

Posted - 2010-04-02 : 14:50:16

Got it all done. Works good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources