Cascading Copy A Row From A Table
May 21, 2008
I need to "cascade clone" or "cascade copy" one row only, in a given database table, but I also need to copy all the child records in related tables, yet preserving the Primary-Foreign key relationships.
Say I have 3 DB Tables.
Companies with a key named CompanyID.
Employees with a key named EmployeeID and a FK to CompanyID.
Projects with a Foreign Key to EmployeeID
One company may contain several employees, and each employee may have several projects.
(See this image for reference www.AgustinGarzon.com/3RelatedTables.jpg )
I need you to point me to a procedure that enables me to copy, for example, one Company record, and have all the associated Employees copied over, and for each Employee, have all the Projects associated copied over.
This is what I call a "Cascade copy", because it's a process that walks down all the database structure, starting from a given field, and looking down through all the database relational hierarchy.
There might be a straight way I can accomplish this task, although I'm unable to find it.
I could write a .net script where I specify the Table Names, the Prikmary Keyname and the Foreign Keyname, but this doesn't sound like a clean solution.
The most important thing is keep a correct Foreign to Primary relationship once the fields have been copied.
I mean, if we clone a company:
1- All the employees should be also copied.
2- All the new employees should relate to the new company primary key.
3- All the projects should be also copied.
4- All the projects should related to the new employee primary key.
I'm attaching an image so you can easily see the pretty simple database structure I'm talking about.
www.AgustinGarzon.com/3RelatedTables.jpg
I'm also attaching the VS2005 project with the SQL Express database.
www.AgustinGarzon.com/TestSolution.zip
Look forward to receiving some tips and links to resources so I can achieve this task, preferably through a SQL stored procedure or an ASP.NET script.
Best Regards.
Agustin Garzon
View 2 Replies
ADVERTISEMENT
Aug 17, 2005
I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?I was hoping someone had experimented and found which works best.
View 2 Replies
View Related
Feb 16, 2008
This function will generate all DELETE statements in correct order to perform a CASCADING delete.
For self-joined tables, it will generate the T-SQL code to "unwind" the table, also in correct order!CREATE FUNCTION dbo.fnCascadingDelete
(
@Schema NVARCHAR(128) = NULL,
@Table NVARCHAR(128) = NULL
)
RETURNS@Return TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
AS
BEGIN
DECLARE@Constraints TABLE
(
RowID INT NOT NULL,
Indent SMALLINT NOT NULL,
[Catalog] NVARCHAR(128) NOT NULL,
[Schema] NVARCHAR(128) NOT NULL,
[Table] NVARCHAR(128) NOT NULL,
[Column] NVARCHAR(128),
pkCatalog NVARCHAR(128),
pkSchema NVARCHAR(128),
pkTable NVARCHAR(128),
pkColumn NVARCHAR(128),
pkType NVARCHAR(128),
pkSize INT,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL
)
INSERT@Constraints
(
RowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
IsSelfJoin,
HasPk
)
SELECTRowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
SelfJoin,
CASE
WHEN [Column] IS NULL THEN 0
ELSE 1
END
FROMdbo.fnTableTree(@Schema, @Table)
IF @@ROWCOUNT = 0
RETURN
DECLARE@SQL TABLE
(
ID INT IDENTITY(1, 1),
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
DECLARE@Indent SMALLINT,
@RowID INT,
@ID INT,
@TSQL NVARCHAR(4000),
@RowSQL NVARCHAR(4000),
@EndSQL NVARCHAR(4000),
@pkColumn NVARCHAR(128),
@IsSelfJoin TINYINT,
@HasPk TINYINT
DECLARE@Unwind TABLE
(
RowID INT NOT NULL,
StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
[SQL] NVARCHAR(4000)
)
WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1)
BEGIN
SELECT TOP 1@RowID = c.RowID,
@ID = c.RowID,
@Indent = c.Indent,
@TSQL = N'',
@EndSQL = N'',
@IsSelfJoin = c.IsSelfjoin,
@HasPk = c.HasPk
FROM@Constraints AS c
LEFT JOIN@SQL AS s ON s.RowID = c.RowID
WHEREs.RowID IS NULL
ORDER BYc.Indent DESC,
c.RowID DESC
WHILE @ID > 0
BEGIN
IF @Indent = 0
SELECT@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)),
@RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)),
@EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''',
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
ELSE
SELECT@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]),
@RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]),
@pkColumn = QUOTENAME(c.pkColumn),
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
SELECT TOP 1@ID = c.RowID,
@Indent = c.Indent,
@RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn,
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID < @ID
AND c.Indent < @Indent
ORDER BYc.Indent DESC,
c.RowID DESC
IF @@ROWCOUNT = 0
SET@ID = 0
SET@TSQL = @RowSQL + @TSQL
END
INSERT@SQL
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
VALUES(
@RowID,
@IsSelfJoin,
@HasPk,
@TSQL + @EndSQL
)
IF @IsSelfJoin = 1
BEGIN
DECLARE@Yak NVARCHAR(160),
@Catalog NVARCHAR(128),
@Column NVARCHAR(128)
SELECT@Yak = pkType + COALESCE('(' + CAST(pkSize AS NVARCHAR(12)) + ')', ''),
@Catalog = [Catalog],
@Schema = [Schema],
@Table = [Table],
@Column = [Column],
@Catalog = [Catalog],
@Table = [Table],
@pkColumn = pkColumn
FROM@Constraints
WHERERowID = @RowID
SET@RowSQL = 'DECLARE@Lvl INT
SET@Lvl = 0
DECLARE@Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey ' + @Yak + ')
INSERT @Stage (Lvl, RowKey) '
+ REPLACE(@TSQL + @EndSQL, 'DELETE t' + CAST(@RowID AS NVARCHAR(12)) + '', 'SELECT 0, t' + CAST(@RowID AS NVARCHAR(12)) + '.' + QUOTENAME(@Column) + '')
+ ' WHILE @@ROWCOUNT > 0
BEGIN
SET@Lvl = @Lvl + 1
INSERT@Stage (Lvl, RowKey)
SELECT@Lvl,
t.' + QUOTENAME(@pkColumn) + '
FROM' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t
INNER JOIN@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + '
AND s.Lvl = @Lvl - 1
LEFT JOIN@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + '
WHEREcr.RowKey IS NULL
END
SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''')
FROM @Stage
WHERE RowID > 0
ORDER BY RowID DESC'
INSERT@Unwind
(
RowID,
[SQL]
)
VALUES(
@RowID,
@RowSQL
)
END
END
INSERT@Return
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
SELECTs.ID,
s.IsSelfJoin,
s.HasPk,
CASE
WHEN u.RowID IS NULL THEN s.[SQL]
ELSE u.[SQL]
END
FROM@SQL AS s
LEFT JOIN@Unwind AS u ON u.RowID = s.RowID
ORDER BYs.ID,
u.StepID
RETURN
ENDE 12°55'05.25"
N 56°04'39.16"
View 16 Replies
View Related
Jan 9, 2008
Hi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
View 3 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Oct 1, 2001
How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies
View Related
Nov 23, 2007
Hi All,
I have 2 tables People & PeopleCosts.
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
Kindly help. thanks in advance.
Myl
View 3 Replies
View Related
Jan 20, 2007
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.
View 6 Replies
View Related
Dec 10, 2007
Hi i have set up two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile table with Id set as the Primery Key as Identity) I
want the user to be able to be able to return to their account at a later date
and then post multiple reviews of different bands they have seen at a later date.
I kept the tables in my example very simple so I could get my head
around the concept, but generally, I want to connect the Id (PK) value in
User_Profile table to the User_Id filed in the User_Review table,
so every review that user writes, will be connected directly to their Id.
Any help you could give would be fantastic a i have no idea where to start!!!
User_Profile
Id int, ( as primary Identity Key)
Name
City
Country
I have a second table called User Reviews
User_Revews
Revew_Id int , ( as primary Identity Key)
User_Id int, ( I want this to contain the Id value in
the User profile Table)
Review_Details
Thanks
Odxsigma
View 3 Replies
View Related
Nov 30, 2004
Hi, All,
I have agentID in product table.
Now I add agentID column in transaction table. Now I want to copy all agentID from product table to transaction table based on the order_id
in both table. Can you show me an example?
Thanks
Betty
View 3 Replies
View Related
Jun 21, 2007
I need to copy existing row data from one table into a new row in a different table, both in the same database. Can this be done in a stored procedure where the selected row is passed in as parameter value?
Thank you,
View 2 Replies
View Related
Apr 24, 2005
Hello everyone,
I have a local MSSQL server (I guess it's called MSDE), with some tables that I would like to use as a template for a set of new tables. I would simply like a Stored Procedure that takes these 3 tables, makes a copy of their structure (not data, since they will be empty), and name them by using a parameter given to the SP. I have made something that I thought would work, but after testing it a bit more, it seems to forget default values for the fields, which is of course not good enough :). I hope that someone can tell me how to duplicate these 3 tables, including every detail for the fields!
View 1 Replies
View Related
Apr 21, 2008
Hi,
I need to recover a table.
I have restored completed database to a temp DB.
I do know,how to use export and import.
Any one can help me?
Regards,
Vinoth
View 2 Replies
View Related
Jun 4, 2007
Hi.Like the title says - how do i do this?I was given the following example:INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'The above statement threw the following error:An explicit value for the identity column in table 'TABLE2' can onlybe specified when a column list is used and IDENTITY_INSERT is ON.Then, after filling in all the column names in my above selectstatement I kept getting an error to the effect that the number ofsource and destination columns don't match. This is because one column"confirm_hash" does not exist in the destination table, just thesource table.could somebody show me how to get this to work?thanks!PS - MS SQL SERVER EXPRESS 2005
View 10 Replies
View Related
Oct 23, 2007
I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.
Table Fields
Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id
The new table is called Tbl_Sales_Growth
Here's the information on the new table:
Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
View 2 Replies
View Related
Jan 8, 2008
Hi,
I have a number of large tables (50,000,000 + rows and 30+ columns) that are refreshed once per month with data from another system. Data is first loaded into a staging table, that has an indentical strcuture to the final table, cleansed and then copy to the final table.
The current copy process is simple but inefficient:
Truncate Table <final-table>
Insert Into <final-table>
Select * from <staging-table>
If possible I don't want to use DTS for this and I'd like to use a batch size of around 10,000 rows to help reduce the size of the log file because I have 3 of these types of Inserts running concurrently.
What is the fatest way to copy data from one table to another, within a stored procedure using SQL 2000 on a large, clustered server that uses logging?
Thanks,
Jason
View 5 Replies
View Related
Mar 1, 2008
hai friends.............. I want to know how to copy table from one database to another in a same sql server 2005? ex : i have one table called sample1 in database db1. we want to copy it and and paste it to database db2.how can i do this explain me elaborately. regards Samuel Chandradoss .J
View 2 Replies
View Related
Apr 3, 2008
How to copy one databse table into another database as a same table with same same tablename?
View 2 Replies
View Related
May 8, 2008
Hi
I have 2 MSSQL databases. I want to copy some tables of DB1 to DB2. how can I do it?
Also, how can I copy some columns of table1 of DB1 to table 2 of DB2?
Thanks for help
View 2 Replies
View Related
Feb 11, 2006
What is the best way to copy a table, with the foriegn keys, primary keys and indexes, from one database to another using SQL Server 2005?
In sql server 2000 I used the DTS, but in SQL server 2005 it does'nt bring over the foriegn keys, primary keys and indexes.Thanks
View 2 Replies
View Related
Feb 16, 2006
Hi everone,I have one table called temp and I am copying its content over to another table called final. The final table has 2 more coloumns than the temp table, 1 of which is a primary key, and the other one is calculated. What is the easiest way I can do this?Thank you for the answer.
View 1 Replies
View Related
Jul 27, 2007
HI All,
i have 2 databases x and y
a table in x named as table1
i am looking to copy 'table1' from database x to database y say named 'table2'
Database y doesnt have a table1
Version: SQL Server 2000
How to do this on Query Analyzer and/or Enterprise Manager???
Thanks
View 3 Replies
View Related
May 6, 2004
what's the best way to copy columns from table1 to table2. i have more columns in table2 than table1, but i want to insert the data from table1 to table2 and add default values to the extra columns....
thanks,
eddie
View 5 Replies
View Related
Nov 24, 2005
Can i copy a table (or its structure) like in Access?
Thanks
View 11 Replies
View Related
Jan 9, 2006
I want to update some values in a table using an update command. Lest I do something stupid and mess things up I would like to first make a copy of that table in case what I do creates some problems down the road.
We have a backup of the whole data base but I would like to make an additional copy of just this one table, just in case. It would also be nice to have just as a record of the before changes state.
How can I do that?
View 2 Replies
View Related
Mar 16, 2006
This is probably a dumb question.. but here goes.
I'd like to make a copy of several tables before changes are made to them so that I can 'roll back' if necessary.
What I 'thought' i could to was this:
select * into mytable_temp from mytable
and then to roll back..
truncate table mytable
Select * into mytable from mytable_temp
When I try and select back into my original table, it says I can't because the object already exists.. What is a better way to accomplish this????
View 3 Replies
View Related
May 24, 2007
Hi friends,
Can anyone help me with a way to copy a table from one Db to another including the contents of the table.
Thank You
joshymraj
View 6 Replies
View Related
Jul 6, 2007
Hi,
I'm using management studio express with a 2005 express server.
Is there an easy way to copy a table from one database to another. In 2000 I would use DTS for this, is there an equivilent?
I know I can copy and paste between tables but the amount of rows is too much for the clipboard and crashes my machine.
Thanks
View 3 Replies
View Related
Feb 23, 2006
Hello all!How to copy a table in MS SQL Server 2000 without chaning a structure? Imean, I have one table, which has autoincrement numeric field (ID). WhenI copy this table by exporting this table into the same database folderI loose the specification of the field ID. Now it is not autoincrementfield but usual int field. Is it possible to copy this table withoutchanging data and structure of a table?Kindest regardsThank youMarcin from Poland*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Oct 12, 2006
I have two identical tables with one IDENTITY column and several othercolumns.I have tested the COPY * INTO table2 FROM table1 WHERE xxbut it requers that table2 does not exist.TABLE2 is a history database of TABLE1How can I copy row(s) from table1 to table2 without conflict withIDENTITY columns using SQL query.Or must I do this from a program (C# .NET)
View 4 Replies
View Related
Jul 20, 2005
hi,How copy two table between two database?copy 1 table1 to 2 table21 - cursor number,2 - cursor number,table1 - sourse tabletable2 - destination tableJaromi
View 2 Replies
View Related
Sep 25, 2006
Gud day guys...i have this problem of copying records within a table...pls help me
I have tried the code below but returned an error.......
"violation of PRIMARY or UNIQUE KEY constraint "INTEG_29" on table "SERVICE" "
Insert into Service (GroupNo,ServiceCode,ServiceName,SystemNo,Upload,timestampstr)
SELECT 6,ServiceCode,ServiceName,SystemNo,Upload,TIMESTAMPSTR
FROM Service
WHERE GroupNo=1
is this possible? pls help me with the sql statement to make this work......
note: timestampstr is the primary key with a not null value ...
View 3 Replies
View Related
Apr 6, 2007
Is there a way to copy a table schema into a new table rather than having to generate a create table script?For example, I have table dbo.MyTable and I want to copy the schema to newuser.MyTable without a script? I know you can restore into a new database from an existing database so maybe you can do this?thanks-c
View 3 Replies
View Related
