September 6, 2011 at 9:39 am
I want to duplicate a row in table 1 changing only the uniqueidentifier (this I can do).
Table 2 has many rows of data attached to this uniqueidentifier in table 1. I would also like to duplicate this data and give it the new uniqueidentifier I assigned in table 1.
I have no clue where to start.
Should I try a cursor or a loop or? Any help would be greatly appreciated.
Thanks for your time!
September 6, 2011 at 12:09 pm
Add a new column in table1 called something like "DuplicateOf" which contains the uniqueidentifier of the row it was copied from. Now you can join table2 to this new column and insert the associated records.
Post some DDL and sample data and I can show you.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
September 6, 2011 at 2:58 pm
Thank you for your reply. I can't add columns to the tables 🙁
Table1
551PopulateYNULL234BF3838A-1795-451F-858B-781061EC2F79
551PopulateYNULL23A37F7A46-90EE-4DAA-9ADA-2B698E191389
551PopulateYNULL23B6B97508-3174-4583-85E5-69877FEBBE2B
551Edit YNULL23"Hell World"
551ActionYNULL23"Print report"
551PopulateYNULL23"Some Data"
Table2
5514BF3838A-1795-451F-858B-781061EC2F79test_lisa {@DP}0
5514BF3838A-1795-451F-858B-781061EC2F79test_sam 0
5514BF3838A-1795-451F-858B-781061EC2F79test_joe90
5514BF3838A-1795-451F-858B-781061EC2F79test_al10
5514BF3838A-1795-451F-858B-781061EC2F79test_al20
5514BF3838A-1795-451F-858B-781061EC2F79{@This}80
551A37F7A46-90EE-4DAA-9ADA-2B698E191389test_al_c10
551A37F7A46-90EE-4DAA-9ADA-2B698E191389test_lisa_s 60
551B6B97508-3174-4583-85E5-69877FEBBE2Btest_thatfin0
declare @new_id varchar(36)
set @new_id = newid()
I want to insert col1, col2, col3, col4, col5 into table1 from table1 where column1=551 and len(col5) <> 36
insert col1, col2, col3, col4, @new_id into table1 from table1 where column1=551 and len(col5) = 36
where len(col5) = 36 I want to find in table2 all those with that uniqueid and duplicate them, the only thing changing is the uniqueid, i want to use the new assocated @new_id that I used in table1.
I hope that makes sense. Thanks again for you time!
September 6, 2011 at 3:11 pm
Could you post your DDL and Sample Data as specified in the first link listed below?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 7, 2011 at 1:00 am
Try below steps:
1. Take the value of newley created unique Id in a variable.
2. Now create a CTE which selects rows from table 2 which matches with previous unique id.
3. Write a INSERT statement to push CTE records in table 2 with a exception to insert Unique Id from variable and not from CTE.
4. loop on unique id's if you have multiple changes.
Hope this will work.
September 7, 2011 at 1:46 am
Please do not be offended but why are you doing this and what will this accomplish?
What Business Requirements will this fulfill?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 7, 2011 at 7:28 am
+1 I would like to know a sample business justification too.
September 7, 2011 at 7:37 am
Thank you all for your replies! As soon as I get time, I will read the links Corgi posted and read about a CTE.
Our office uses templates (forms). These templates use fields to store data. You can create triggers on these templates (100s if you like and within each trigger, you can define sub actions). This is all done in the software that it came with which is pretty basic. Most of the form editing is easier done in SQL.
I would like to copy a set of triggers and sub triggers from one field on one template to another field on another template.
I've been experimenting with nested while loops and nested cursors with out any luck. I'm wondering if a cursor inside a loop or a loop inside a cursor would be best. Maybe the CTE thing is my best approach...
If/when I figure this out, I will share the solution...
September 7, 2011 at 8:30 pm
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#table1','U') IS NOT NULL
DROP TABLE #table1
--===== Create the test table with
CREATE TABLE #table1
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
template_id int,
trig_name varchar(20),
action varchar(50),
trig_order int,
parm2 varchar(400),
enable_ind char(1),
create_timestamp datetime,
created_by int
)
INSERT INTO #table1 (template_id, trig_name, action, trig_order,
parm2, enable_ind, create_timestamp, created_by)
select '4133','action','Assign Field Value','3','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4133','click','populate field','4','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4133','Select','Assign Field Value','5','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','click','picklist','1','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','entry','click','1','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','1','""','N','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','2','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','3','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','4','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','5','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','6','""','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','7','7E9F6B95-2F52-4E00-97DD-EE16FDE7B6BD','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','8','"Converted"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','9','"Converted"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','10','"Converted"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','11','"Converted"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','12','"Converted"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','13','"Converted"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','14','"Converted"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','15','495F0157-F884-4386-A2C2-B48C3C752A8B','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','16','"CALD"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','17','"CHCM"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','18','"Army"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','19','"PVD"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','20','BF789045-E31F-4E75-9DD7-4D134B25E2B7','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','21','"Other"','Y','Feb 9 2010 10:26PM','686' UNION ALL
select '4175','List Pick','Assign Field Value','22','"Risk"','Y','Feb 9 2010 10:26PM','686'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#table2','U') IS NOT NULL
DROP TABLE #table2
--===== Create the test table with
CREATE TABLE #table2
(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
template_id int,
trig_id uniqueidentifier,
field_name varchar(60),
field_value varchar(400),
seq_nbr int,
enabled_flag char(1))
INSERT INTO #table2 (template_id, trig_id, field_name, field_value, seq_nbr, enabled_flag)
select '4175','BF789045-E31F-4E75-9DD7-4D134B25E2B7','Filter5','"Valve"','0','Y'UNION ALL
select '4175','495F0157-F884-4386-A2C2-B48C3C752A8B','Filter8','"Imported"','0','Y'UNION ALL
select '4175','7E9F6B95-2F52-4E00-97DD-EE16FDE7B6BD','filter7','""','0','Y'UNION ALL
select '4175','7E9F6B95-2F52-4E00-97DD-EE16FDE7B6BD','Filter8','""','1','Y'UNION ALL
select '4175','7E9F6B95-2F52-4E00-97DD-EE16FDE7B6BD','discrete','""','1','Y'UNION ALL
select '4175','7E9F6B95-2F52-4E00-97DD-EE16FDE7B6BD','Filter8','""','1','Y'UNION ALL
select '4175','7E9F6B95-2F52-4E00-97DD-EE16FDE7B6BD','Filter9','""','1','Y'
September 7, 2011 at 8:39 pm
I hope the above is what you were asking for.
I'm not sure how to proceed with the CTE or how to get the previous unique id(s)).
September 12, 2011 at 4:04 pm
Nooooob (9/6/2011)
I want to duplicate a row in table 1 changing only the uniqueidentifier (this I can do).Table 2 has many rows of data attached to this uniqueidentifier in table 1. I would also like to duplicate this data and give it the new uniqueidentifier I assigned in table 1.
I have no clue where to start.
Should I try a cursor or a loop or? Any help would be greatly appreciated.
Thanks for your time!
If this is a one off and you have the relevant permissions, I guess you could create copies of the tables and alter the foreign key constraints to cascade updates.
Change the uniqueidentifiers of any rows you choose and the changes should also be made for you in the foreign key tables.
You can then copy back the rows you require to the original table.
I'll be glad to help out if you are willing to put up readily consumable test data and ddl.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply