Copying records in tables that are linked

[Saturn57]

I have two tables one is the header and one is the detail. I need to be able to copy specific corresponding records from both tables and create new records that have new primary keys. Im not sure how to do this as I have primary keys in both tables and one of the fields in the detail table in a reference to the primary key in header table. Failing misserably! Any suggestions

 

[bborissov]

Not sure at all what you need.
Why you need to copy records that already exists?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Saturn57]

Sometimes we have to quote a similar product for different customers so we copy the previous quote instead of reinputting all the data.

 

[bborissov]

Could you post the data and desired result?
The easiest way it to create a SP, pass old ID from the header file, then within transaction insert new record in the header file, use SCOPE_IDENTITY() to get newly created ID, and INSERT detail records with that new ID as Parent.
That is true ONLY if you use an IDENTITY field as primary key in header file.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Saturn57]

Here is my data. Note estimateno is the primary key in the first table and estno is the primary key in the second table

My header table:


USE [Estimator]
GO
/****** Object: Table [dbo].[QuoteCalcHeader] Script Date: 03/23/2009 15:19:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuoteCalcHeader](
[estimateno] [int] IDENTITY(1,1) NOT NULL,
[filelockbit] [bit] NULL,
[date] [datetime] NULL CONSTRAINT [DF__QuoteCalcH__Date__398D8EEE] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))),
[customer] [nvarchar](50) NULL,
[contact] [nvarchar](50) NULL,
[partno] [nvarchar](50) NULL,
[partname] [nvarchar](50) NULL,
[englev] [nvarchar](50) NULL,
[materialname] [nvarchar](50) NULL,
[mattype] [nchar](100) NULL,
[matthick] [decimal](8, 3) NULL CONSTRAINT [DF__QuoteCalc__Mater__3A81B327] DEFAULT ((0)),
[blankl] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3B75D760] DEFAULT ((0)),
[blankw] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3C69FB99] DEFAULT ((0)),
[blankp] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Blank__3D5E1FD2] DEFAULT ((0)),
[makesnoparts] [int] NULL CONSTRAINT [DF__QuoteCalc__Makes__3E52440B] DEFAULT ((0)),
[oem] [nvarchar](50) NULL,
[program] [nvarchar](50) NULL,
[partdepth] [decimal](10, 2) NULL CONSTRAINT [DF__QuoteCalc__Model__3F466844] DEFAULT ((0)),
[tapeno] [nvarchar](50) NULL,
[revcomments] [nvarchar](3000) NULL,
[terms] [nvarchar](100) NULL CONSTRAINT [DF__QuoteCalc__Terms__403A8C7D] DEFAULT ('10%Design,25%Material,55%After Tryout At Saturn Prior To Delivery,10% Net 30 Days'),
[currency] [nvarchar](50) NULL,
[exchange] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalc__Excha__412EB0B6] DEFAULT ((0)),
[delivery] [int] NULL CONSTRAINT [DF__QuoteCalc__Deliv__4222D4EF] DEFAULT ((0)),
[fob] [nvarchar](50) NULL CONSTRAINT [DF__QuoteCalcHe__FOB__4316F928] DEFAULT ('Saturn'),
[jobno] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[postalcode] [nvarchar](50) NULL,
[rfqno] [nvarchar](50) NULL,
[comments] [nvarchar](3000) NULL,
[partpic] [image] NULL,
[homelinecost] [money] NULL,
[transport] [money] NULL,
[carrycost] [money] NULL,
[discount] [decimal](5, 2) NULL CONSTRAINT [DF_QuoteCalcHeader_discount] DEFAULT ((0)),
[english] [bit] NULL,
[procpic1] [image] NULL,
[procpic2] [image] NULL,
[procpic3] [image] NULL,
[procpic4] [image] NULL,
[procpic5] [image] NULL,
[procpic6] [image] NULL,
CONSTRAINT [PK_QuoteCalcHeader] PRIMARY KEY CLUSTERED
(
[estimateno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


My detail table:


USE [Estimator]
GO
/****** Object: Table [dbo].[QuoteCalcBody] Script Date: 03/23/2009 15:21:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuoteCalcBody](
[estno] [int] NOT NULL CONSTRAINT [DF__QuoteCalc__Estim__7C8480AE] DEFAULT ((1.45)),
[operno] [int] IDENTITY(1,1) NOT NULL,
[filelockbit] [bit] NULL,
[quantity] [int] NULL CONSTRAINT [DF__QuoteCalc__Quant__7D78A4E7] DEFAULT ((0)),
[englishunits] [bit] NULL,
[diename] [nvarchar](50) NULL,
[diel] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die L__7E6CC920] DEFAULT ((0)),
[diew] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die W__7F60ED59] DEFAULT ((0)),
[diesh] [decimal](10, 3) NULL CONSTRAINT [DF__QuoteCalc__Die S__00551192] DEFAULT ((0)),
[density] [decimal](5, 2) NULL CONSTRAINT [DF__QuoteCalc__Densi__014935CB] DEFAULT ((0)),
[system] [nvarchar](20) NULL,
[construction] [nvarchar](20) NULL,
[noofops] [int] NULL CONSTRAINT [DF__QuoteCalc__No Of__023D5A04] DEFAULT ((0)),
[optyp] [nvarchar](20) NULL,
[dod] [nvarchar](20) NULL CONSTRAINT [DF__QuoteCalcBo__DOD__03317E3D] DEFAULT ((0)),
[cnccomplex] [nvarchar](20) NULL CONSTRAINT [DF__QuoteCalc__DSFac__0425A276] DEFAULT ((0)),
[pins] [int] NULL,
[wear] [int] NULL,
[dieworkareapercentage] [decimal](5, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dieworkareapercentage] DEFAULT ((1)),
[lengthoftrim] [decimal](7, 2) NULL,
[diesections] [int] NULL,
[nitrogenupper] [bit] NULL,
[nitrogenlower] [bit] NULL,
[aircushion] [bit] NULL,
[noofsections] [int] NULL,
[noofholes] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __07F6335A] DEFAULT ((0)),
[qtyacs] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __09DE7BCC] DEFAULT ((0)),
[qtyacm] [int] NULL,
[qtyacl] [int] NULL,
[qtydmcs] [int] NULL CONSTRAINT [DF__QuoteCalc__# Of __0BC6C43E] DEFAULT ((0)),
[qtydmcm] [int] NULL,
[qtydmcl] [int] NULL,
[coatingcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_coatingcost] DEFAULT ((0)),
[specialitems] [nvarchar](50) NULL,
[specialitemcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Speci__0DAF0CB0] DEFAULT ((0)),
[basicmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_basicmatcost] DEFAULT ((0)),
[patternmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_patternmatcost] DEFAULT ((0)),
[pinmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_pinmatcost] DEFAULT ((0)),
[wearmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_wearmatcost] DEFAULT ((0)),
[holematcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Hole __08EA5793] DEFAULT ((0)),
[dmcmatcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Die M__0CBAE877] DEFAULT ((0)),
[acmatcost] [money] NULL CONSTRAINT [DF__QuoteCalc__Aeria__0AD2A005] DEFAULT ((0)),
[nitrogenmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_nitrogenmatcost] DEFAULT ((0)),
[sectionmatcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_sectionmatcost] DEFAULT ((0)),
[totalmaterialcost] [money] NULL CONSTRAINT [DF_QuoteCalcBody_totalmaterialcost] DEFAULT ((0)),
[basicprochrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicprochrs] DEFAULT ((0)),
[totalprochrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalprochrs] DEFAULT ((0)),
[basicsimhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicsimhrs] DEFAULT ((0)),
[totalsimhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalsimhrs] DEFAULT ((0)),
[basicdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicdsgnhrs] DEFAULT ((0)),
[pindsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pindsgnhrs] DEFAULT ((0)),
[weardsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_weardsgnhrs] DEFAULT ((0)),
[nitrodsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitrodsgnhrs] DEFAULT ((0)),
[holedsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holedsgnhrs] DEFAULT ((0)),
[acdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_acdsgnhrs] DEFAULT ((0)),
[dmcdsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmcdsgnhrs] DEFAULT ((0)),
[sectiondsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sectiondsgnhrs] DEFAULT ((0)),
[totaldsgnhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totaldsgnhrs] DEFAULT ((0)),
[basic2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic2chrs] DEFAULT ((0)),
[pin2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pin2chrs] DEFAULT ((0)),
[wear2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wear2chrs] DEFAULT ((0)),
[nitro2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitro2chrs] DEFAULT ((0)),
[hole2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hole2chrs] DEFAULT ((0)),
[ac2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_ac2chrs] DEFAULT ((0)),
[dmc2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmc2chrs] DEFAULT ((0)),
[sec2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sec2chrs] DEFAULT ((0)),
[trim2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_trim2chrs] DEFAULT ((0)),
[total2chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total2chrs] DEFAULT ((0)),
[basic2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic2dhrs] DEFAULT ((0)),
[pin2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pin2dhrs] DEFAULT ((0)),
[wear2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wear2dhrs] DEFAULT ((0)),
[nitro2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitro2dhrs] DEFAULT ((0)),
[hole2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hole2dhrs] DEFAULT ((0)),
[ac2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_ac2dhrs] DEFAULT ((0)),
[dmc2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmc2dhrs] DEFAULT ((0)),
[section2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_section2dhrs] DEFAULT ((0)),
[trim2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_trim2dhrs] DEFAULT ((0)),
[total2dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total2dhrs] DEFAULT ((0)),
[basic3chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic3chrs] DEFAULT ((0)),
[total3chrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total3chrs] DEFAULT ((0)),
[basic3dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basic3dhrs] DEFAULT ((0)),
[total3dhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_total3dhrs] DEFAULT ((0)),
[basicassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicassyhrs] DEFAULT ((0)),
[pinassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_pinassyhrs] DEFAULT ((0)),
[wearassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_wearassyhrs] DEFAULT ((0)),
[nitroassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_nitroassyhrs] DEFAULT ((0)),
[holeassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holeassyhrs] DEFAULT ((0)),
[acassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_acassyhrs] DEFAULT ((0)),
[dmcassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dmcassyhrs] DEFAULT ((0)),
[sectionassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_sectionassyhrs] DEFAULT ((0)),
[totalassyhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalassyhrs] DEFAULT ((0)),
[basicspothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basicspothrs] DEFAULT ((0)),
[holespothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holespothrs] DEFAULT ((0)),
[totalspothrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalspothrs] DEFAULT ((0)),
[basictryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_basictryhrs] DEFAULT ((0)),
[holetryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_holetryhrs] DEFAULT ((0)),
[totaltryhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totaltryhrs] DEFAULT ((0)),
[totalhrs] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_totalhrs] DEFAULT ((0)),
[prochradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_prochradj] DEFAULT ((0)),
[simhradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_simhradj] DEFAULT ((0)),
[dsgnhradj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_dsgnhradj] DEFAULT ((0)),
[hrs2cadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs2cadj] DEFAULT ((0)),
[hrs2dadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs2dadj] DEFAULT ((0)),
[hrs3cadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs3cadj] DEFAULT ((0)),
[hrs3dadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_hrs3dadj] DEFAULT ((0)),
[assyadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_assyadj] DEFAULT ((0)),
[spotadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_spotadj] DEFAULT ((0)),
[tryadj] [decimal](7, 2) NULL CONSTRAINT [DF_QuoteCalcBody_tryadj] DEFAULT ((0)),
[processprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_processprice] DEFAULT ((0)),
[simulationprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_simulationprice] DEFAULT ((0)),
[designprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_designprice] DEFAULT ((0)),
[price2c] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price2c] DEFAULT ((0)),
[price2d] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price2d] DEFAULT ((0)),
[price3c] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price3c] DEFAULT ((0)),
[price3d] [money] NULL CONSTRAINT [DF_QuoteCalcBody_price3d] DEFAULT ((0)),
[assyprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_assyprice] DEFAULT ((0)),
[spotprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_spotprice] DEFAULT ((0)),
[tryprice] [money] NULL CONSTRAINT [DF_QuoteCalcBody_tryprice] DEFAULT ((0)),
[diediscount] [decimal](18, 2) NULL CONSTRAINT [DF__QuoteCalcBod__Ds__1367E606] DEFAULT ((0)),
[pricecdnprediscount] [money] NULL CONSTRAINT [DF__QuoteCalc__Price__0EA330E9] DEFAULT ((0)),
[pricecdnpostdiscount] [money] NULL CONSTRAINT [DF_QuoteCalcBody_pricecdnpostdiscount] DEFAULT ((0)),
[priceforeignpostdiscount] [money] NULL CONSTRAINT [DF_QuoteCalcBody_priceforeignpostdiscount] DEFAULT ((0)),
[cdndolperhr] [money] NULL,
[matpercofprice] [decimal](5, 2) NULL,
CONSTRAINT [PK_QuoteCalcBody_1] PRIMARY KEY CLUSTERED
(
[operno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

[bborissov]

O!, I would NOT write the whole script for this (too many fields )
But it should be something like this:

CREATE PROCEDURE InsertNewCustomer(
       @filelockbit bit,
       @date datetime,
       @customer nvarchar(50),
       @contact  nvarchar(50),
...........
(w/o [estimateno]),
       @OldDetailId int)
AS
  BEGIN
      BEGIN TRANSACTION
         DECLARE @sc_id int
         DECLARE @nError int
         INSERT INTO QuoteCalcHeader (field list here w/o [estimateno])
         VALUES
         (parameter list here w/o the last one)
         SET @nError = @@ERROR
         IF @nError = 0
            BEGIN
               SET @sc_id = SCOPE_IDENTITY()
               INSERT INTO QuoteCalcBody
               SELECT @sc_id, -- that comes instead of [estno] field
                      the rest of the field list
               FROM QuoteCalcBody
               WHERE estno = @OldDetailId
               SET @nError = @@ERROR
            END
         IF @nError= 0
            COMMIT TRANSACTION
         ELSE
           ROLLBACK TRANSACTION
   END

NOT TESTED!!!!!!!!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Saturn57]

a couple of questions what does w/o mean?
How does sc_id get the proper number. When you call it Scope Identity does it automatically know it is the primary key?
How do I get the new field number for estimateno in the header table into the detail table. I don't see that in your code?

 

[bborissov]

1. w/o means without.

Your field estimateno in header table is defined as int IDENTITY(1,1). That means with each insert into that table it increase its value by one.
The SCOPE_IDENTITY() function get the last value of the last identity field increased in the current batch. It does NOT care if that identity field is Primary key or not. But if you have insert only in header table before you call that function that means you will get the right value of the estimateno. Just check what BOL said about SCOPE_IDENTITY(). And to prevent question BOL means Books On-Line (SQL Server HELP system). Just press F1 and search for SCOPE_IDENTITY()

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I like this link

http://blog.sqlauthority.com/2007/0...nt-retrieve-last-inserted-identity-of-record/