query update problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

query update problem

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2011-04-21 : 00:32:56

I have a master table,BM with fields: bmID, val, date
and a detail table BAM with fields: bamID, bmID, val date, others

I need to make copies of records in the BM table, changing their value and date and appending the copy back to the BM table. These will have new bmID's since bmID is the primary and an identity key.

Then I have to make a copy of all records in BAM that have the oldparentID of the records (the old bmID) that I just appended to the parent table. I have to change the bmID in these BAM -detail table records to the new BmID of the records I appened to BM table. Then I do a few other things, change date, etc. and append those back to the BAM table. What I am doing is preparing a new year of records using a selection of records from the previous year from both tables.

The only way I could do this is having a new field in the BM table called oldbmID. That would contain the old ID so I could link the records in the BAM-detail table in order to do the copy and append to that table.

Is there another better way?

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-21 : 07:17:13

something like
declare @maxid int, @minid int
select @maxid = max(id)+1 from BM
select @minid = min(id) from BM

select bmID, val, date, newid = @maxid-@minid+bmID into #a from BM
set identity_insert BM on
insert BM (bmID,val,date) select newID, val+10, dateadd(dd,20,date) from #a
set identity_insert BM off
insert BAM (bmID, val date, ...)
select a.newID, BAM.val+20, dateadd(dd,5,BAM.date, ...)
from #a a
join BAM
on #a.bmID = BAM.bmID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

smh
Yak Posting Veteran

94 Posts

Posted - 2011-04-21 : 11:18:59

I see. By using the set identity insert, you can maintain the newID's in the temp table instead of a new field. Great idea.

I shall try this.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources