Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedure

Status
Not open for further replies.

dbware

MIS
Jul 25, 2001
6
CA
I would like to know how I could write the following stored procedure

step 1 read the data from database1
step 2 add the results from step 1 to database2. Note that fields in database1 are not necessary the same name as the fields in database2.
step 3 write into a field from step 1 results that the information was added to database2.

Thank you for your help

dbware
 
Use a cursor to accomplish this task. The values in the brackets are replaceable values as well as the variables.
Keywords are capitalize

CREATE PROCEDURE [sp_name] AS

'Declare variables
DECLARE @var1 char(1),
@var2 int

'Define the cursor
DECLARE [cursor_name] CURSOR FOR
SELECT var1, var2 FROM [database1..tablename]
'Open the cursor
OPEN [cursor_name]
'Fetch first row

FETCH NEXT FROM [cursor_name] INTO @var1, @var2
'Iterate through the cursor row-by-row

WHILE (@@FETCH_STATUS=0)
BEGIN
BEGIN TRANSACTION
INSERT INTO [database2..tablename] (field1, field2)
VALUES (@var1, @var2)
'If error
IF (@@ERROR <> 0)
GOTO TRANS_ABORT

COMMIT TRANSACTION
FETCH NEXT FROM [cursor_name] INTO @var1, @var2
END
INSERT INTO [database3..tablename] (field1)
VALUES ('Database insert complete - ' + GetDate())
GOTO DONE

TRANS_ABORT:
ROLLBACK TRANSACTION

DONE:
CLOSE [cursor_name]
DEALLOCATE [cursor_name]
 
I'm not sure you need a cursor for this...

INSERT database1.dbo.tableX (field1, field2, field3)
SELECT f1, f2, f3
FROM database2.dbo.tableY
WHERE ...

IF @@ERROR = 0
UPDATE database1.dbo.tableX
SET fieldn = <whatever>
WHERE ... same where clause as above

This assumes that both databases are on the same server. If not, then you need to append the servername at the from of the table name path and set up linked servers.
 
Balves,

I am assuming that he wants to move ALL the data from an existing database to another existing database. Perhaps there is another way besides a cursor to move all the data. Of course if he is just moving a record then it would hold true that a cursor is not needed.


Gary
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top