By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

re-insert / templating records

P: n/a
I have a problem. The project that I'm working on requires me to duplicate
records. As in a series of records are entered into the system the user then
click on a button to make these as 'Templates' so that they would not have
to re-enter alot of the information. So from a db perspective I would have
to re-insert these records into the database. The problem is there're over
20 tables and the relationship is complex. 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 ?

or is there a easy way to do this ?

Thanks
Tom
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
--
Jul 23 '05 #2

P: n/a
Tom Gao (to********@optushome.com.au) writes:
I have a problem. The project that I'm working on requires me to
duplicate records. As in a series of records are entered into the system
the user then click on a button to make these as 'Templates' so that
they would not have to re-enter alot of the information. So from a db
perspective I would have to re-insert these records into the database.
The problem is there're over 20 tables and the relationship is complex.
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 ?


There's a whole lot of information missing here, but in any case, the
answer is: no.

Are you inserting into the same table, or from a table with templates?
Well, in most cases it makes sense to store templates in the same table
as the real rows.

But then there are at least two columns that are not to be copied to
the new rows: the key and the column that marks that the template is a
template.

And I would not be surprised if there are more columns. For instance,
say that there are auditing columns who tells which which user that
created the row and when. Such data is of course not possible to
inherit from the client.

So, I am sorry, you just have to start coding. And pay attention to
the business requirements, so that you copy what you should copy, no
more, no less.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.