Stored Proc to copy master records along with their multiple details
I am using SQL Server 2005. I am writing a stored procedure. I have 2 Pairs of tables, each Pair having a master-detail relationship. And each table contains a defined, unique Numeric field that is manually assigned an ID. (NOTE: The last ID used for each table, is maintained in a separate table that is queried and updated via a separate stored procedure we can call "GetNextID" with Input parameter TableName and Output parameter NextID.) Lets refer to the Pairs of tables as: M1 and D1, M2 and D2 --------------------------------- Table [M1] =========== IDM1 NUMERIC(10) NOT NULL ACCTNO VARCHAR( 6) NOT NULL ACCTNAME VARCHAR(35) Table [D1] =========== IDD1 NUMERIC(10) NOT NULL IDM1LINK NUMERIC(10) NOT NULL ADDR1 VARCHAR( 6) NOT NULL ADDR2 VARCHAR( 6) CITY VARCHAR( 6) NOT NULL STATE VARCHAR( 6) NOT NULL Table [M2] =========== IDM2 NUMERIC(10) NOT NULL ACCTNO VARCHAR( 6) NOT NULL ACCTNAME VARCHAR(35) Table [D2] =========== IDD2 NUMERIC(10) NOT NULL IDM2LINK NUMERIC(10) NOT NULL ADDR1 VARCHAR( 6) NOT NULL ADDR2 VARCHAR( 6) CITY VARCHAR( 6) NOT NULL STATE VARCHAR( 6) NOT NULL --------------------------------- I want to copy a specific set of records from M2, along with all of their linked D2 records (if the M2 and/or D2 records should exist for certain conditions), to the M1 and D1 tables, while maintaining the original master-detail relationship between M and D records. Thank you in advance for your time and help.
There are a number of ways you could do this. But, If you have to generate each ID value though, you can only ever do this process row-by-row, so you'll have to use a cursor. Do you have the capability to generate sets of identifiers? Without out, assuming we're moving sets of data, there's no efficient way to do the move. The next problem is how are you determining the records that must be moved? Is it a comma delimited list or something similar? Or are you only ever going to move one M2 record and the linked set of D2 records? Assuming you can generate a set of identifiers and you have a listing of the identifiers to move, it's just a matter of managing the data to ensure you can match the old id's to the newly generated id's. I'd suggest using a temp table to store that information and then you can just JOIN to it while using an INSERT.. SELECT statement to move the data. But it all comes down to the ability to generate sets of identifiers and provide some list of the stuff to move.
Hello Grant, Thank you for your reply. A little more background on my situation...our DBA (who is very good and efficient in what he does) is on vacation, and I, with beginner-level SQL knowledge, was given his duties temporarily, one of which was to fix a stored procedure he had created before leaving on vacation. He incorporated the master table into the stored procedure, but left out the linked detail records (that may or may not exist). Getting back to your questions; Although no Set of identifiers are generated, the stored procedure does contain a temp table (as you suggested) to store multiple records from the master table (i.e. M2). And that temp table does contain an INTEGER IDENTITY (0,1) column (IDtemp) that will be used to help increment the actual ID values in a future Insert statement. The set of master records to be moved (inserted into the temp table) are obtained via an INSERT from a conditional SELECT of table M2. Should the @@ROWCOUNT resulting from that INSERT statement be greater than 0, then: 1) the next ID (IDnext) is obtained via a call to stored procedure GetNextID 2) an INSERT into table M1 from a SELECT of the temp table is executed; whereas, IDM1 is assigned IDtemp + IDnext I can try to simplify the code and present it for clarity, which I am sure would help a lot, but I apparently do not know how to implement a single carriage return on this message board, so it could look messy. Maybe you know how to do it? Or it must always be a double carriage return? What is missing from the code, is the Inserting of the linked detail records (0, 1, or more) along with the Insertion of the master records.