troubleshooting Question

How do I write a tsql stored procedure to copy data from many tables into one table?

Avatar of MartinPetex
MartinPetex asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
9 Comments1 Solution237 ViewsLast Modified:
I am attempting to copying data from columns within many tables of a badly designed database into the same columns but just within one table of a newly created database. I'm hoping to run this procedure as a daily job (so I have daily up to date data to play with) as the old database is still required by our admin dept (creating/updating/deleting records etc) until I have fully tested my new design. What approach should I take for this? I've been reading into cursors (example attached which populates a null column (Matter_Uno) in one table from another table on a foreign key value: MatterNo) but I'm not too sure if this is the easiest approach?

Thanks.
ALTER PROCEDURE [Trusts].[proc_MatterNoMatterUno] 

AS
DECLARE @trust_id int
DECLARE @trust_matter_no NVARCHAR(65)
DECLARE @trust_matter_uno int

DECLARE  CursorTemplate CURSOR 
      FAST_FORWARD FOR       
      Select TrustID, MatterNo
	  From Trusts.Trusts
	  Where Matter_Uno is null
		
 
OPEN CursorTemplate
 
FETCH NEXT FROM CursorTemplate 
INTO     @trust_id, @trust_matter_no
 
WHILE (@@FETCH_STATUS = 0)
BEGIN
        SET @trust_matter_no = lower(@trust_matter_no)

	    Select top 1 @trust_matter_uno = Matter_Uno
	    From hbm_matter
		Where MATT_CODE = @trust_matter_no

		Update Trusts.Trusts
		Set Matter_Uno = @trust_matter_uno
		Where TrustID = @trust_id
 
      FETCH NEXT FROM CursorTemplate 
      INTO     @trust_id, @trust_matter_no
 
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate

RETURN
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros