How to insert a failed record in SQLServer due to primary and foreign key constraints

You intend to import customer data from the customer table and country table of the SQLServer2000 database to the SQLServer2005 database. Make sure that each value in the country code column of the customer table has a corresponding record in the China table of the SQLServer2005 database during the import process. You define a foreign key between two tables, so that the integrity of the reference will be guaranteed.

You plan to import customer data from the customer table and country table of the SQL Server 2000 database to the SQL Server 2005 database. Make sure that each value in the country code column in the client table has a corresponding record in the SQL Server 2005 database China table during the import process. You define a foreign key between two tables, so that the integrity of the reference will be guaranteed.

You plan to import customer data from the customer table and country table of the SQL Server 2000 database to the SQL Server 2005 database. Make sure that each value in the country code column in the client table has a corresponding record in the SQL Server 2005 database China table during the import process. You define a foreign key between two tables. in this way, the integrity of the reference will ensure that if the country code value exists in the customer table and the country table does not exist, the import program fails. Make sure that the import process does not fail when there are no records in the country table. <无>

-- Create Department (deptID, deptName) and UserInfo (userID, userName, sex, loginDate, deptid) -- deptID and userID are automatically increased and primary keys. Deptid is a foreign key. And insert test data -- create sequence seq_Dep start with 1 increment by 1 nomaxvalue nocycle cache 30; -- create table DepartmentCREATE TABLE Department (deptID VARCHAR2 (10) primary key, deptName VARCHAR2 (20); -- INSERT test data insert into Department VALUES ('D' | seq_Dep.NEXTVAL, 'HR Department '); insert into Department VALUES ('D' | seq_Dep.NEXTVAL, 'technical Department '); insert into Department VALUES ('D' | seq_Dep.NEXTVAL, 'logistics Department'); insert into Department VALU ES ('D' | seq_Dep.NEXTVAL, 'managerial authorization'); insert into Department VALUES ('D' | seq_Dep.NEXTVAL, 'sales authorization '); insert into Department VALUES ('D' | seq_Dep.NEXTVAL, 'service'); insert into Department VALUES ('D' | seq_Dep.NEXTVAL, 'authorization'); COMMIT; -- create sequence seq_User start with 1 increment by 1 nomaxvalue nocycle cache 30; -- create table UserInfoCREATE TABLE UserInfo (userID VARCHAR2 (10) primary key, userName VARCHAR2 (20), sex VA RCHAR2 (2), loginDate DATE, deptid VARCHAR2 (10) REFERENCES Department (deptID); -- INSERT test data INTO UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Eric Schmidt ', 'male', '12-September-07', 'd1 '); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Larry page ', 'male', '12-October-07 ', 'd3'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Sergey brin', 'male ', '12-November-07 ', 'd5'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'W. M. coughran, Jr. ', 'male', '1-December-07', 'd4 '); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'David C. drummond ', 'female', '12-December-01 ', 'D2'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Alan Eustace ', 'male', '12-September-07 ', 'd1'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Jeff Huber ', 'male ', '12-October-07 ', 'd3'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'George Reyes ', 'male ', '12-September-07 ', 'd5'); INSER T into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Elliot Schrage ', 'male', '1-August 7-07', 'd4 '); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Tim Armstrong ', 'female', '12-January 1, December-01', 'D2 '); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'nikesh Arora ', 'femal', '12-January 1, December-01', 'D2 '); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'sukhinder ', 'male', '12-September 07', 'd1 '); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Vin Ton G. cerf', 'male', '12-October-07 ', 'd3'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'George Reyes ', 'male', '12-November-07 ', 'd5'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Dave Girouard ', 'male ', '1-September-07 ', 'd4'); insert into UserInfo VALUES ('u' | seq_User.NEXTVAL, 'Singh Cassidy ', 'female ', '12-September-01', 'D2 '); COMMIT; -- 3. create index deptid_index on UserInfo (deptid); -- 4. create synonyms for Department and UserInfo, The names are dept, sy_userCREATE or replace synonym dept FOR Department; create or replace synonym sy_user FOR UserInfo;/* 5. you can use dept and user synonyms to create a view. The View requires that you can query "department name, department number, user name, and gender, registration time "*/create or replace view view_dept_user (" Department name "," Department ID "," user name "," gender "," registration time ") as select d. deptName, d. deptID, u. userName, u. sex, u. loginDate FROM dept d, sy_user u WHERE d. deptID = u. deptid; -- 14. use the Instead Of trigger to insert data to the View created in question 5th. Set serveroutput on; insert into view_dept_user ('Department name', 'Department number', 'username', 'gender ', 'registration time') VALUES ('Storage amount ', 'd6 ', 'Dejan Perkovic', 'male', '1-August 8-08 ');/* create or replace view view_dept_user ("Department name", "Department No ", "user name", "gender", "registration time") as select d. deptName, d. deptID, u. userName, u. sex, u. loginDate FROM dept d, sy_user u WHERE d. deptID = u. deptid; */create or replace trigger dept_user_insertINSTEAD of insert on view_dept_userFOR EACH ROWD Eclare cursor cur_dept is select * FROM Department WHERE Department. deptID =: NEW. deptID; CURSOR cur_user is select * FROM sy_user WHERE sy_user.userName =: NEW. userName; d cur_dept % rowtype; u cur_user % rowtype; did dept. deptID % TYPE; uid sy_user.userID % TYPE; begin open cur_dept; OPEN cur_user; FETCH cur_user INTO u; FETCH cur_dept INTO d;/* if the department ID in the inserted data does not exist, assign the sequence number generated by seq_Dep.NEXTVAL to the variable did and execute the insert statement. One department. */IF cur_dept % notfound then did: = 'd' | seq_Dep.NEXTVAL; insert into Department (deptID, deptName) VALUES (did,: NEW. deptName);/* IF the employee in the inserted data does not exist, execute the insert statement to add a new employee */IF cur_user % notfound then uid: = 'U' | seq_User.NEXTVAL; insert into UserInfo (userID, userName, sex, loginDate, deptid) VALUES (uid,: NEW. userName,: NEW. sex,: NEW. loginDate, did); end if; IF cur_dept % found then/* IF the inserted data contains an existing department ID, update the department name */di D: =: NEW. deptID; UPDATE Department SET Department. deptName WHERE Department. deptID =: NEW. deptID;/* IF the employee in the inserted data does not exist, assign the serial number generated by seq_User.NEXTVAL to the variable uid, and execute the insert statement to add a new employee */IF cur_user % notfound then uid: = 'U' | seq_User.NEXTVAL; insert into UserInfo VALUES (uid,: NEW. userName,: NEW. sex,: NEW. loginDate,: NEW. deptID); ELSE/* if an employee already exists in the inserted data, the value of other fields of the employee is updated based on the department ID and employee name. The employee ID is the primary key and does not need to be updated. Considering the possibility of an employee with the same name, but the employee ID cannot be the same, you need to query the ID of the employee that meets the requirements based on the department ID and employee name in the inserted data, use employee ID for subsequent operations */SELECT userID INTO uid FROM UserInfo WHERE UserInfo. userName =: NEW. userName AND UserInfo. deptID = did; UPDATE UserInfo SET UserInfo. sex =: NEW. sex, UserInfo. loginDate =: NEW. loginDate, UserInfo. deptID =: NEW. sex WHERE UserInfo. userID = uid AND UserInfo. userName =: NEW. userName; end if; CLOSE ecur; CLOSE dcur; END dept_us Er_insert;/show errors; error prompt: Warning: the created trigger has a compilation error. SQL> show errors; TRIGGER DEPT_USER_INSERT ERROR: LINE/COL ERROR -------- ----------------------------------------------------------- 3/31 PLS-00049: incorrect value variable 'NEW. DEPTID '5/30 PLS-00049: incorrect value variable 'NEW. USERNAME '20/56 PLS-00049: incorrect value variable 'NEW. DEPTNAME '26/75 PLS-00049: incorrect value variable 'NEW. USERNAME '26/89 PLS-00049: incorrect value variable 'NEW. SEX '26/98 PLS-00049: incorrect value variable 'NEW. LOGINDATE '33/10 PLS-00049: incorrect value variable 'NEW. DEPTID '34/4 PL/SQL: SQL Statement ignored34/46 PL/SQL: ORA-00927: missing equal sign 34/72 PLS-00049: incorrect value variable' NEW. DEPTID '40/37 PLS-00049: incorrect value variable 'NEW. USERNAME 'line/col error -------- ----------------------------------------------------------------- 40/51 PLS-00049: incorrect value variable 'NEW. SEX '40/60 PLS-00049: incorrect value variable 'NEW. LOGINDATE '40/75 PLS-00049: incorrect value variable 'NEW. DEPTID '47/66 PLS-00049: incorrect value variable 'NEW. USERNAME '49/40 PLS-00049: incorrect value variable 'NEW. SEX '49/70 PLS-00049: incorrect value variable 'NEW. LOGINDATE '49/103 PLS-00049: incorrect value variable 'NEW. SEX '50/53 PLS-00049: incorrect value variable 'NEW. USERNAME '55/5 PLS-00103: the sign "DEPT_USER_INSERT" appears when you need the following: if