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.
Author |
Topic |
taj
Starting Member
39 Posts |
Posted - 2013-06-19 : 01:03:23
|
Hi all,Requirement: 1)Table Name: EmpColumn Names: EmpID int,Ename varchar(20),Deptid int,Locid intNote: EmpID is primary key (not identity) DeptID is foreign Key with Dept table LocID is foreign key with Loc table2)Table Name: DeptColumn Names: Deptid int,Dname varchar(20)Note: Deptid is primary key (not identity)3)Table Name: LocColumn Name: Locid int,Lname varchar(20)Now i will create a view to get the full employee details from all above mentioned tables.create view vwemployeeasselect E.Empid,E.Ename,D.Dname,L.Lnamefrom Emp Einner join Dept Don E.DeptID=D.DeptIDinner join Loc Lon E.LocID=L.LocIDNow when i execute above created view my Result Will be EmpID,Ename,Dname,Lnameso this same result as it is i want to insert into one more table mentioned below4) Table Name: EmployeesColumn Names: Empid int,Ename varchar(20),Dname varchar(20),Lname varchar(20)IS IT POSSIBLE TO WRITE A TRIGGER ON VIEW,IF IT IS THEN WHICH TRIGGER I HAVE TO WRITE TO PERFORM FOR ABOVE REQUIREMENT? ORIS IT POSSIBLE TO WRITE A SINGLE STORED PROCEDURE FOR BOTH SELECTING DATA FROM MULTIPLE TABLES AS MENTIONED ABOVE AND THEN INSERTING THE SAME INTO MY 4th TABLE (EMPLOYEES)?solutions with queries for the above requirement will be appreciated.Thank you |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 01:09:13
|
its possible to write a trigger on view but i didnt understand purpose for that. For your requirement what you need is just a batch sql statement or a procedure where you can do the inserting of data from three table to fourth tableit will be likeCREATE PROCEDURE InsertEMployeesASINSERT INTO Employeesselect E.Empid,E.Ename,D.Dname,L.Lnamefrom Emp Einner join Dept Don E.DeptID=D.DeptIDinner join Loc Lon E.LocID=L.LocIDWHERE NOT EXISTS (SELECT 1 FROM EMployees WHERE Empid = E.Empid) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
taj
Starting Member
39 Posts |
Posted - 2013-06-19 : 02:09:15
|
thanks for your quick reply....i will surely try with your suggested solution....Actually my requirement is, i have to write a single SP which does select and insert operation, I have 4 to 5 tables which i have to join and then insert the same into one table.NOTE:As there is some entry into select table,then the same should be automatically inserted into the insert table.For this requirement i thought of creating a view and then creating a trigger on the view which insert the data from view into my resultant table.If you don't mind can you give me the query for creating the trigger on view which can perform my requirement.IS IT POSSIBLE FOR TO WRITE A SINGLE TRIGGER ON MULTIPLE TABLES.AS THERE IS SOME ENTRY INTO MULTIPLE TABLES,THEN IT SHOULD INSERT THE SAME INTO ONE TABLE?Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 02:18:34
|
no need of trigger again. My posted suggestion does the select and insert in the same step which is what you want as per your explanation given belowi have to write a single SP which does select and insert operation, I have 4 to 5 tables which i have to join and then insert the same into one table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
taj
Starting Member
39 Posts |
Posted - 2013-06-19 : 02:43:04
|
I have tried this as you suggested,but i'm unable to fix it...please have look and help me out in fixing this.create table memp (empid int,ename varchar(20),deptid int,locid int) create table mdept (deptid int,dname varchar(20)) create table mloc (locid int,lname varchar(20)) create table memployees (empid int,ename varchar(20),dname varchar(20),lname varchar(20)) SELECT * FROM memp 1 SHAIK 1 12 ABHI 2 23 MAHESH 3 34 SIRAJ 4 45 RAVI 5 56 KUMAR 6 6 SELECT * FROM mdept 1 IT2 HR3 PAYROLL4 FINANCE5 TESTING6 R&D7 ADMIN SELECT * FROM mloc 1 INDIA2 USA3 UK4 UAE5 AFRICA6 PAKISTAN7 AP Create PROCEDURE InsertEMployeesASINSERT INTO memployeesselect E.Empid,E.Ename,D.Dname,L.Lnamefrom memp Einner join mdept Don E.DeptID=D.DeptIDinner join mloc Lon E.LocID=L.LocIDWHERE NOT EXISTS (SELECT * FROM memployees WHERE empid = E.Empid) INSERT INTO memp VALUES(7,'RAJ',7,7)select * from memployeesstill my memployees table is empty, it has to show me the previous and latest inserted records.Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 02:50:14
|
it has to show me the previous and latest inserted records.where did this part come from? You didnt specify it yet.also in your above code you've just created procedure, you didnt execute itCreate PROCEDURE InsertEMployeesASINSERT INTO memployeesselect E.Empid,E.Ename,D.Dname,L.Lnamefrom memp Einner join mdept Don E.DeptID=D.DeptIDinner join mloc Lon E.LocID=L.LocIDWHERE NOT EXISTS (SELECT * FROM memployees WHERE empid = E.Empid)Go--now execute it asEXEC InsertEMployeesGo-- now check the result select * from memployees--new valueINSERT INTO memp VALUES(7,'RAJ',7,7)--now execute it do the insertEXEC InsertEMployeesGo-- noe check result againselect * from memployees ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
taj
Starting Member
39 Posts |
Posted - 2013-06-19 : 03:58:06
|
oh sorry for not executing the stored procedure...thanks a lot once again for your help....i need one more small help from you.....SELECT * FROM MEMPEmpID Ename DeptID LocID1 SHAIK 1 12 ABHI 2 23 MAHESH 3 34 SIRAJ 4 45 RAVI 5 56 KUMAR 6 68 RAJU 5 49 RAJU 5 4suppose if i have more columns compared to above memp table in my memployee tableSelect * from memployeesEmpID Ename DeptID LocID Pname1 SHAIK IT INDIA NULL2 ABHI HR USA NULL3 MAHESH PAYROLL UK NULL4 SIRAJ FINANCE UAE NULL5 RAVI TESTING AFRICA NULL6 KUMAR R&D PAKISTANNULL8 RAJU TESTING UAE NULL9 RAJU TESTING UAE NULLthe previously created stored procedure will give an error saying that Msg 213, Level 16, State 1, Procedure InsertEMployees, Line 3Column name or number of supplied values does not match table definition.Because No of columns in both the tables did not match.Now i want to alter my previously created stored procedure in such a way that if No. of columns did not match....then the non-matching columns should be stored with null value...to do this what condition i have to pass in my stored procedure.Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 04:03:19
|
[code]Create PROCEDURE InsertEMployeesASINSERT INTO memployees(EmpID, Ename, DeptID, LocID, Pname)select E.Empid,E.Ename,D.Dname,L.Lname,NULLfrom memp Einner join mdept Don E.DeptID=D.DeptIDinner join mloc Lon E.LocID=L.LocIDWHERE NOT EXISTS (SELECT * FROM memployees WHERE empid = E.Empid)Go[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
taj
Starting Member
39 Posts |
Posted - 2013-06-19 : 06:00:12
|
Thanks much for your help.....if i have not null columns ex: salary in my memployee table, then what condition i have to pass in my stored procedure.IS IT POSSIBLE TO INSERT EMPTY SPACE IN NOT NULL COLUMNS?Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 06:04:08
|
quote: Originally posted by taj Thanks much for your help.....if i have not null columns ex: salary in my memployee table, then what condition i have to pass in my stored procedure.IS IT POSSIBLE TO INSERT EMPTY SPACE IN NOT NULL COLUMNS?Thank you
its possible as empty space ('') is not same as NULLNULL represents a condition of unknown value and its not actually stored as a value whereas '' is a value by itselfin your case, since it is a Salary field you should be using a numeric value as default (0,-1 etc depending on your business definition)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
taj
Starting Member
39 Posts |
Posted - 2013-06-20 : 02:21:41
|
thanks much for your last reply......CREATE PROCEDURE InsertEMployeesASINSERT INTO mEmployees(Empid,Ename,dname,lname)select E.Empid,E.Ename,D.Dname,L.Lnamefrom mEmp Einner join mDept Don E.DeptID=D.DeptIDinner join mLoc Lon E.LocID=L.LocIDWHERE NOT EXISTS (SELECT Empid,Ename,dname,lname FROM EMployees WHERE Empid = E.Empid)From the above insert query in my insert table i have one more column sampleid bigint(notnull),this sampleid column is not available in any other tables(memp,mdept,mloc) so that i can join it and get the sampleid. so as of now i want to show the sampleid as 0(zero)in my resultant table(memployees).so want changes i have to make in my above stored procedure code. Plz suggest me.....Thank You |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-20 : 02:42:12
|
[code]CREATE PROCEDURE InsertEMployeesASINSERT INTO mEmployees(Empid,Ename,dname,lname,sampleid)select E.Empid,E.Ename,D.Dname,L.Lname,0from mEmp Einner join mDept Don E.DeptID=D.DeptIDinner join mLoc Lon E.LocID=L.LocIDWHERE NOT EXISTS (SELECT Empid,Ename,dname,lname FROM EMployeesWHERE Empid = E.Empid)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|