?
Solved

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

Posted on 2009-12-23
9
Medium Priority
?
222 Views
Last Modified: 2013-11-10
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

Open in new window

0
Comment
Question by:MartinPetex
  • 4
  • 4
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26113978
you just need to use the following update


Update t
            Set Matter_Uno = ( Select top 1 @trust_matter_uno = Matter_Uno    From edinpmsql.cmsnet.dbo.hbm_matter     Where CLNT_MATT_CODE = t.trust_matter_no )
FROM Trusts.Trusts t
Where Matter_Uno is null
0
 

Author Comment

by:MartinPetex
ID: 26114018
and I will be able to pin your update to a daily job?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26114068
yes..

test it first on a dev server before you implement it on live server
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:MartinPetex
ID: 26118252
aneeshattingal,

So is the attached effectively all I need?...

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)
      Update t
            Set Matter_Uno = ( Select top 1 @trust_matter_uno = Matter_Uno    From edinpmsql.cmsnet.dbo.hbm_matter     Where CLNT_MATT_CODE = t.trust_matter_no )
      FROM Trusts.Trusts t 
      Where Matter_Uno is null
 
      FETCH NEXT FROM CursorTemplate 
      INTO     @trust_id, @trust_matter_no
 
END
 
CLOSE CursorTemplate
DEALLOCATE CursorTemplate

RETURN

Open in new window

0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26120627
You dont need a cursor here


ALTER PROCEDURE [Trusts].[proc_MatterNoMatterUno]
AS
Update t
            Set Matter_Uno = ( Select top 1 @trust_matter_uno = Matter_Uno    From edinpmsql.cmsnet.dbo.hbm_matter     Where CLNT_MATT_CODE = t.trust_matter_no )
FROM Trusts.Trusts t
Where Matter_Uno is null
0
 

Author Comment

by:MartinPetex
ID: 26137048
aneeshattingal,

On revision, I think it would be more like the attached, as I don't think there's a need to set parameters in my procedure. It's also become a requirement for me to set three different columns if they are all originally null, I guess the attached is the cleanest way this can be done?

Thanks.
ALTER PROCEDURE [dbo].[proc_import_crmdb_data] 

AS
Update t
Set t.jobtitle = 
	( 
		Select top 1 jobtitle   
		From edinpmsql.cmsnet.dbo.hbm_matter     
		Where CLNT_ID = t.PersonID 
	)
Set t.firstname = 
	( 
		Select top 1 firstname    
		From edinpmsql.cmsnet.dbo.hbm_matter     
		Where CLNT_ID = t.PersonID 
	)
Set t.surname = 
	( 
		Select top 1 surname   
		From edinpmsql.cmsnet.dbo.hbm_matter     
		Where CLNT_ID = t.PersonID 
	)
FROM Trusts.Trusts t 
Where jobtitle is null
AND firstname is null
AND surname is null

RETURN

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26138465
MartinPetex,

seems fine to me

Aneesh
0
 

Author Comment

by:MartinPetex
ID: 26138664
aneeshattingal,

I seem to get errors on line 11, 17 and 23 ....incorrect syntaxt near '.'?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 26144048
Should be more like this:
note the syntax difference - there is only one set, and the columns are seperated by commas.
 
Please award aneesh the points rather than myself.

ALTER PROCEDURE [dbo].[proc_import_crmdb_data]  
 
AS 
Update t 
Set t.jobtitle =  
        (  
                Select top 1 jobtitle    
                From edinpmsql.cmsnet.dbo.hbm_matter      
                Where CLNT_ID = t.PersonID  
        ),
t.firstname =  
        (  
                Select top 1 firstname     
                From edinpmsql.cmsnet.dbo.hbm_matter      
                Where CLNT_ID = t.PersonID  
        ),
t.surname =  
        (  
                Select top 1 surname    
                From edinpmsql.cmsnet.dbo.hbm_matter      
                Where CLNT_ID = t.PersonID  
        ) 
FROM Trusts.Trusts t  
Where jobtitle is null 
AND firstname is null 
AND surname is null 
 
RETURN

Open in new window

0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question