Copy data into a table & data linked from another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy data into a table & data linked from another

Author

Topic

proone23
Starting Member

10 Posts

Posted - 2011-09-01 : 23:13:34

insert into table1 (column1, column2, column3)
select 'newdata', column2, newid()
from table1 where column1 = '1' and len(column3) = 36
(26 rows affected)

table2 may or may not have 1 or more rows referencing column3 in table1

I want to make duplicates in table2 that correspond to the duplicates I made in table1.

Can someone point me in the right direction?

A visual example
table1 (column1, column2, UNIQUEIDENTIFIER22)
table2 (UNIQUEIDENTIFIER22, somedata, somedata)
(UNIQUEIDENTIFIER22, somemoredata, somemoredata)
(UNIQUEIDENTIFIER22, otherdata, otherdata)
(UNIQUEIDENTIFIER22, data, data)

table1 (column1, column2, UNIQUEIDENTIFIER44)
table2 (UNIQUEIDENTIFIER44, data1, data2)

Thanks in advance!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 23:45:07

how will you input the somedata,some other data etc? or in other words how will you determine number of values to be linked to one UNIQUEIDENTIFIER value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

proone23
Starting Member

10 Posts

Posted - 2011-09-02 : 09:37:41

I want to make duplicates of what is already there, the only thing changing is the uniqueidentifier that ties it to what I am duplicating in table 1.

ie

declare @p1 uniqueidentifier set @p1 newid()

table1 (john, doe, 9C388D6A-BFBC-48A4-883B-9E21633D30E3)

table2 (newport, la, 9C388D6A-BFBC-48A4-883B-9E21633D30E3)
table2 (bismarck, nd, 9C388D6A-BFBC-48A4-883B-9E21633D30E3)
table2 (fargo, nd, 9C388D6A-BFBC-48A4-883B-9E21633D30E3)

copy from table1 into table1 and make all associated data in table2 follow along:

table1 (jane, doe, @p1)

table2 (newport, la, @p1)
table2 (bismarck, nd, @p1)
table2 (fargo, nd, @p1)

I have no idea where to start to accomplish this...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 12:52:29

[code]
insert table2
select col1,col2,@p1
from table2
..
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

proone23
Starting Member

10 Posts

Posted - 2011-09-02 : 13:30:38

I was hoping to not do it manually. I have hundreds to copy in table1 (I don't mind doing this part manually) but the info attached in table2 could be up to 40 rows. (300 * 40 = way too many sql statements). There must be some way to automate this but I don't now what to search for to help me. I don't know if I need joins or unions or...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 13:46:28

but all hundreds of value will come from table 1 itself right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

proone23
Starting Member

10 Posts

Posted - 2011-09-02 : 13:59:39

Yes, I will manually copy from table1 into table1 but wherever there is a uniqueidentifier in column 3, I want to insert a new uniqueidentifier and make duplicates of everything in table2 that are linked to that uniqueidentifier with that new uniqueidentifier being the only thing that changes in the data I am copying in table2. (I hope that makes sense)...

proone23
Starting Member

10 Posts

Posted - 2011-09-02 : 14:30:22

After alot of searching, would I (could I) use nested cursors? Would that be the easiest?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 20:34:02

for copying everything from table1 to itself for new identifier, just use like

insert into table1
select other columns, @newuniqueidentifier
from table1
where idfield=@olduniqueidentifier

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

proone23
Starting Member

10 Posts

Posted - 2011-09-06 : 11:32:00

That part I figured out but I was hoping to also duplicate the data in table2 that is attached to table 1.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 11:45:21

that will be like

insert into table2
select other columns, @newuniqueidentifier
from table1
where idfield=@olduniqueidentifier

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources