Duplicating rows in a table should never be necessary or desirable and it
shouldn't even be possible since evey table should have unique/primary key
constraints that prevent this. I assume therefore you will want to maintain
uniqueness by changing some column values. Unfortunately you haven't told us
anything about keys, constraints or the data you want to modify.
Is there a way to copy the parent
the record and have sqlserver automatically cascade and re-insert all
related and referenced records back into the database ?
I guess here that you are talking about copying rows between tables with
IDENTITY columns. This is easy provided you have declared natural (not
IDENTITY) keys on the tables. IDENTITY should not be the only key of a
table. Here is an example of moving a parent entity and its related rows
between tables while maintaining the surrogate keys.
CREATE TABLE Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE /* Note the Key */)
CREATE TABLE Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE /* Note the Key */, employeename VARCHAR(30) NOT
NULL, deptid INTEGER NOT NULL REFERENCES Departments (deptid))
CREATE TABLE New_Departments (deptid INTEGER IDENTITY PRIMARY KEY, deptname
VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE New_Employees (employeeid INTEGER IDENTITY PRIMARY KEY, ssn
CHAR(10) NOT NULL UNIQUE, employeename VARCHAR(30) NOT NULL, deptid INTEGER
NOT NULL REFERENCES New_Departments (deptid))
INSERT INTO New_Departments (deptname)
SELECT D.deptname
FROM Departments AS D
LEFT JOIN New_Departments AS N
ON D.deptname = N.deptname
WHERE N.deptname IS NULL
INSERT INTO New_Employees (ssn, employeename, deptid)
SELECT E1.ssn, E1.employeename, D2.deptid
FROM Employees AS E1
JOIN Departments AS D1
ON E1.deptid = D1.deptid
JOIN New_Departments AS D2
ON D1.deptname = D2.deptname
LEFT JOIN New_Employees AS E2
ON E1.ssn = E2.ssn
WHERE E2.employeeid IS NULL
--
David Portas
SQL Server MVP
--
