<g class="gr_ gr_54 gr-alert gr_gramm Grammar multiReplace" data-gr-id="54" id="54">I want</g> to get row number of last inserted into employee table?
How can i get it @@identity function returns null.
is there any way to do it?
I run into a problem when asking to show a query of employee vacation days.
table 1:
column1 is dates
e.g.
2015-01-01
2015-01-02
2015-01-03
.
.
.
2015-12-31
table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012
My query output need to be:
2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV)
1X
1Y
Results Table(SQL)
CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
2015-08-04 08:35:29.203 successfully inserted value 1Y
2015-08-04 08:30:29.203 Test Pass
2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output:
All values from values table are inserted successfully
Total count of values inserted: 2
If only few values are inserted, example only 1X from Values table is inserted in Message
Example:
Results Table CreatedDate Message
2015-08-04 08:45:29.203 successfully inserted value 1X
2015-08-04 08:44:29.103 TEst pass
2015-08-04 08:43:29.103 successfully inserted value 1X
2015-08-04 08:42:29.203 test point
Output:
All values from values are not inserted successfully in result table.
Total count of values inserted: 1
Missing Values not inserted in results table are: 1Y
I would like to perform autodeletion operation from my table using stored procedure....
Here is my table structure:
Emp.id | emp_name | dept | desig | time_out | date | emp_sign |
if the user inserts new employee record that record should delete automatically from original after
30 minutes..I don't want to keep that newly inserted record after
30 minutes...I don't know how to achieve this...
hi
I want to do a "bulk" insert and then get the id (primary key) of the inseret rows:
insert into table1 select * from table2
If I get the value of the @@identity, I always get the last inserted record.
Any idea how to get the ids of all inserted values?
Thx
Olivier
I want to get the employee hierarchy like tree structure and reportes.
View 7 Replies View RelatedCREATE TABLE [dbo].[multiple](
[emp_code] [varchar](20) NULL,
[swip_in] [nvarchar](255) NULL,
[swipe_in_date] [nvarchar](255) NULL,
[swipe_out] [nvarchar](255) NULL,
[Swipe_out_date] [nvarchar](255) NULL
[Code] ....
I want result like below:
Empcode swip_in1, swip_out1, swip_in2, swip_out2, swip_in3, swip_out3
Condition :
1. 24 hrs who are multiple time punched
I have a database with two tables (Table1 and Table2)
looking for something like:
Table1:
+----+-------+----------+
| id | Name | email |
+----+-------+----------+
| 1 | name1 | mail1 |
| 2 | name2 | mail2 |
| 3 | name3 | mail3 |
| 4 | name4 | mail4 |
| 5 | name5 | mail5 |
| 6 | name6 | mail6 |
+-- +-------+----------+
Table2:
+----+------------+---------+------+
| id | table1_ID | fee | type |
+---+-------------+---------+------+
| 1 | 1 | 20000 | 1 |
| 2 | 3 | 1000 | 1 |
| 3 | 1 | 10000 | 1 |
| 4 | 3 | 1000 | 1 |
| 5 | 5 | 500 | 1 |
| 6 | 5 | 500 | 2 |
| 7 | 1 | 60000 | 2 |
| 8 | 2 | 1000 | 2 |
+----+------------+---------+-------+
i want the query that return:
+--------+-------+---------------------------------+---------------------------------+--------+
| name | email | a:sum(fee) where type=1 | b:sum(fee) where type=2 | b/10 |
+--------+-------+---------------------------------+---------------------------------+--------+
|name1 | mail1 | 30000 | 60000 | 6000|
|name2 | mail2 | 0 | 1000 | 100 |
|name3 | mail3 | 2000 | 0 | 0 |
|name4 | mail4 | 0 | 0 | 0 |
|name5 | mail5 | 500 | 500 | 50 |
|name6 | mail6 | 0 | 0 | 0 |
+--------+-------+---------------------------+---------------------------------------+---------+
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
Dear All,i want to know how to get top three salary getters from the employee(eid , ename, salary) table
i tried this select top 3 salary from employee order by salary desc
but it gives me top three salary record say there is salary 1000,1200,1300,1300,1500then my query return me 1500,1300,1200 whereas i want to 1500,1300,1300,1200
how can i do it
please help
thanks
I have an Employee table that has
EmployeeID (PK)
SupervisorID (which is really just another EmployeeID)
..random junk...
Now that part makes sense, everyone gets one and only one boss.
Their boss can change, and therefore the SupervisorID would be updated.
Now I have an EmployeeEvals table that has quarterly evaluation data.
I want to relate these two tables.
Eval table has
EvalID (PK)
ReviewedEmployeeID (the one being evaluated)
SupervisorID (the one doing the evaluation)
Now I need to link this back to the employee table (at least I think I do).
So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...
But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like
EvalID: 12345
ReviewedEmployeeID: 55
SupervisorID: 55
which of course wouldn't happen as an employee wouldn't evaluate themself.
How do I handle the relationships for this properly?
Do I just not link the SupervisorID back to anything?
How Can I select Table Dynamically from in Side SQL Query
i.e.,
Select * from (Here I want Select the Dynamically from other Query)
DECLARE @Query varchar(8000)
Create Table
#tempCountRichard (Status varchar(50), Number Varchar(1000)
Set @Query = 'Insert Into #tempCountRichard
Select Count(Status),
[code]...
With the following SQL. When I select from the temp table I return the right count but my second column doesn't return anything.
Count Status
1010 2000
1111 2222
When I run the query that is being inserted into the the temp table I return the correct results
Count Status
1010 Pass
2000 Pass with Obs
1111 Fail
2222 None
how to select the data from the temp table exactly the same way it was inserted. As I need to select the exact same data from the insert.
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
I want to select custom format from table?
View 7 Replies View RelatedI have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
I have a TableA where data get inserted from Excel(IMPORT/EXPORT wizard)
TableA;
ID(identity) Date (NOT NULL Defaulyt Getdate() )
Name Phone
1 2014-06-17 17:28:21.190
Nick 12345678910
2 2014-05-17 17:28:21.190
Stan 00045678910
3 2015-08-17 17:28:21.190
Kim 11111678910
4 2015-08-17 17:28:21.190
Tom NUll
3,4 are the rows i have inserted now , you can see by date, likewise i have 100,000 rows(old and new combination) and now the data from excel to TableA can be imported/exported daily , hourly, weekly basis.
Now i want to find out only the rows which are imported to tableA today, or hours back, or yesterday .....
the reason is ,
Step1:get data from excel and import to tableA( this is a manual Step) and i know when the data is inserted with exact date and time.
Step2: get newly inserted rows from TableA and pass them as Parameters in Stored Procedure.( i may run step 2 after 1 hour, or after 1 day or after 1 week ,but i want only rows that are inserted )
I tried with using where Datecreated, but did work.
Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins
Note:- also need records who doesn't have manager (null)
table structure
eid------ename------mgrid
1------Nancy------2
2------Andrew------null
3------Janet ------2
4------Margaret------2
5------Steven------4
6------Michael ------5
o/p
Employee------Manager
Nancy------Andrew
Andrew------Null
Janet ------Andrew
.
.
Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table
and I'm obviously not having much luck
This is basically the table layout, note each employee can have multiple rows with different dates
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
01/04/07
40
1
Jones
Tom
01/.12/07
50
1
Jones
Tom
04/01/08
60
2
Smith
Ed
02/14/05
70
2
Smith
Ed
03/18/06
80
3
Brown
John
06/23/04
80
3
Brown
John
12/23/04
79
3
Brown
John
01/07/06
50
3
Brown
John
10/22/08
69
What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
04/01/08
60
2
Smith
Ed
03/18/06
80
3
Brown
John
10/22/08
69
I've looked at select distinct and the date operatives with out any success.
Thanks Much
Vince
I'm trying to fill a temp table whose columns are the same as another table plus it has one more column. The temp table's contents are those rows in the other table that meet a particular condition plus another column that is the name of the table that is the source for the rows being added.
Example: 'permTable' has col1 and col2. The data in these two rows plus the name of the table from which it came ('permTable' in this example) are to be added to #temp.
Data in permTable
col1 col2
11, 12
21, 22
Data in #temp after permTable's filtered contents have been added
TableName, col1 col2
permTable, 11, 12
permTable, 21, 22
What is the syntax for an insert like this?
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
I was thinking that this question probably comes up a lot...but my forum searches didn't turn anything up, so here goes:
I have an id field that auto increments (identity) for each record.
I insert a record.
How can I safely extract the id field of the record for use in another query, avoiding the possiblity that another record is inserted before I can get at it?
Thanks,
G
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
I have one table with primary key index, when i want to select the result from this particular table, i wish the result arrange by record inserted squence and not by the primary key i had been set.
View 4 Replies View RelatedI have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created
CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)
The following stmt won't work...
INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C
... as the number of supplied values does not match table definition.
I would like to wrap the following code in a function and reuse it. I use this code in many triggers.
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
THEN 'U' -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I' -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D' -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete".
END)
Is it possible to write a function and pass the INSERTED and DELETED logical tables to it?
Hi,
Check this SQL
SELECT DISTINCT
TblOrder.CustomerUID,
TblOrder.OrderHiddenID,
TblPayment.PaymentAmount,
TblPayment.Result,
TblOrder.OrderID
FROM TblOrder
CROSS JOIN TblPayment
WHERE (TblOrder.CustomerUID = @IsCustomerID)
AND (TblOrder.OrderHiddenID = @IsHiddenID)
AND (TblPayment.Result = 'Pending')
AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))
one customer can have more than one orders.
So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View 2 Replies View RelatedI am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View 2 Replies View RelatedI want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?