August 13, 2008 at 1:55 pm
I am trying to create a stored procedure that will read records from a source table and insert the data into two separate table, where the child table has a FK constraint on the parent table. I don't want to use a cursor and I have no idea where to start
Source Table
FName LName PrimeAddressLn1 PrimeCity PrimeState
Tim Smith 1234 Main St Buffalo NY
Tim Smith 14345 19Th Ave New York NY
Bob Jones 4356 7TH St Oakwood CA
NewCustomer Table
RecordID(Inentity) FName LName
1 Tim Smith
2 Bob Jones
NewAddress Table
RecordID AddressLn1 City State
1 1234 Main St Buffalo NY
1 14345 19Th Ave New York NY
2 4356 7TH St Oakwood CA
-----------------------------------------------------
Source Table - This is where the data is coming from
CREATE TABLE [SrcCustomer](
[FName] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[LName] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PrimeAddressLn1] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PrimeCity] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PrimeState] [Char(2)](25) COLLATE Latin1_General_CI_AS NULL,
(
----------------------------------------------------------
New Customer Table - Destination for the customer infromation
CREATE TABLE [NewCustomer](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[LName] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_NewCust] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
----------------------------------------------------------
New Address Table - Destination for the address information
CREATE TABLE [Address](
[RecordID] [int] NOT NULL,
[AddressLn1] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[City] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[State] [Char(2)](25) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [Address] WITH CHECK ADD CONSTRAINT [FK_Address_DM_NewCustomer] FOREIGN KEY([RecordID])
REFERENCES [Address] ([RecordID])
August 13, 2008 at 2:15 pm
Just a quick note, you can't have a Primary Key constraint on RecordID in your Address table if you plan to allow for a Customer to have multiple Address rows like in your example. You'll need to another IDENTITY column if you want a surrogate key. You can call it AddressID. Once you drop the PK constraint from the RecordID column, the following code will get you going.
INSERT INTO dbo.NewCustomer (FName,LName)
SELECT DISTINCT FName,
LName
FROM dbo.SrcCustomer
INSERT INTO dbo.Address
SELECT RecordID,
PrimeAddressLn1,
PrimeCity,
PrimeState
FROM dbo.SrcCustomer src
INNER JOIN dbo.NewCustomer Cust ON src.FName = Cust.FName AND src.LName = Cust.LName
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply