November 16, 2009 at 10:10 am
I have to import data from tblCustSource and tblCustAddressSource into tblCust and tblCustAddress
As each record inserts into tblCust, I need to grab the identity and use that to insert (what may be) multiple address records.
The code below does the correct recort insert into the two tables, but the custid that gets inserted is ALWAYS the max custid...so my second statement has something wrong with it.
Example...if the max scope_identity() value = 2000 the all records that get inserted into tblCustAddress will wind up with a custid value of 2000.
insert into tblCust( FirstName, LastName ) Select FirstName, LastName from dbo.tblCustSource
insert into tblCustAddress(custID, addrTypeID, Addr1, Addr2)
SELECT
scope_identity(), addrTypeID, Addr1, Addr2 from dbo.tblCustAddressSource
where dbo.tblCustAddressSource.custid In (SELECT custID FROM dbo.tblCustSource )
November 16, 2009 at 10:17 am
Trying to figure out without the table structure and some sample data would be difficult.
I encourage you to post the create table scripts and also some sample insert scripts (5-10 records)
and then explain your desired result.
PS. try posting the Scripts using the Code Tags for better readability
like select x from y
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 16, 2009 at 10:25 am
from BOL ;
SCOPE_IDENTITY
Returns the last identity value inserted into an identity column in the same scope
so you will always get the max id when used in that way.
I would not use SCOPE_IDENTITY, instead opt for a set-based approach with an inner join for the second insert.
INSERT INTO tblCustAddress
(
custID,
addrTypeID,
Addr1,
Addr2
)
SELECT tblCustSource.custid,
addrTypeID,
Addr1,
Addr2
FROM dbo.tblCustAddressSource
INNER JOIN tblCustSource ON tblCustAddressSource.custid = tblCustSource.custid
November 16, 2009 at 10:43 am
My code is below...
The new tables getting inserted ito will have some data already and the custID is the pKey...so I HAVE to pick that up each time for the custAddress destination table.
My code is marked below to build the test platform
If I run THIS it places the source custID into the address table...I need the new custid
/***********TEST INSERT #1********************/
--======================================================================
insert into tblCust( FirstName, LastName ) Select FirstName, LastName from dbo.tblCustSource
INSERT INTO tblCustAddress
(
custID,
addrTypeID,
Addr1,
Addr2
)
SELECT scope_identity(),
addrTypeID,
Addr1,
Addr2
FROM dbo.tblCustAddressSource
INNER JOIN tblCustSource ON tblCustAddressSource.custid = tblCustSource.custid
Select * from tblCust
Select * from tblCustAddress
--=========================================================
If I run this it places the max custID in all records in the tblCustAddress...which is incorrect as well.
/***********TEST INSERT #2********************/
--======================================================================
insert into tblCust( FirstName, LastName ) Select FirstName, LastName from dbo.tblCustSource
insert into tblCustAddress(custID, addrTypeID, Addr1, Addr2)
SELECT
scope_identity(), addrTypeID, Addr1, Addr2 from dbo.tblCustAddressSource
where dbo.tblCustAddressSource.custid In (SELECT custID FROM dbo.tblCustSource )
Select * from tblCust
Select * from tblCustAddress
--=========================================================
--TEST TABLES AND TEST DATA-------------------------------
/****** Object: Table [dbo].[tblCust] Script Date: 11/16/2009 12:19:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCust](
[custID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
CONSTRAINT [PK_tblCust] PRIMARY KEY CLUSTERED
(
[custID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCustAddress](
[addrID] [int] IDENTITY(1,1) NOT NULL,
[custID] [int] NULL,
[addrTypeID] [int] NULL,
[Addr1] [varchar](50) NULL,
[Addr2] [varchar](50) NULL,
CONSTRAINT [PK_tblCustAddress] PRIMARY KEY CLUSTERED
(
[addrID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCustSource](
[custID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
CONSTRAINT [PK_tblCustSource] PRIMARY KEY CLUSTERED
(
[custID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblCustAddressSource](
[addrID] [int] IDENTITY(1,1) NOT NULL,
[custID] [int] NULL,
[addrTypeID] [int] NULL,
[Addr1] [varchar](50) NULL,
[Addr2] [varchar](50) NULL,
CONSTRAINT [PK_tblCustAddressSource] PRIMARY KEY CLUSTERED
(
[addrID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
declare @id int
insert into tblCustSource(FirstName,LastName) VALUES('Larry', 'Brister')
set @id=scope_identity()
insert into dbo.tblCustAddressSource(custID, addrTypeID, Addr1, Addr2) VALUES(@id, 1, 'Addr1', 'Addr2')
declare @id2 int
insert into tblCustSource(FirstName,LastName) VALUES('Larry', 'Tally')
set @id2=scope_identity()
insert into dbo.tblCustAddressSource(custID, addrTypeID, Addr1, Addr2) VALUES(@id2, 1, 'Addr1', 'Addr2')
insert into dbo.tblCustAddressSource(custID, addrTypeID, Addr1, Addr2) VALUES(@id2, 2, 'Addr1', 'Addr2')
November 16, 2009 at 10:52 am
Steve is right. You should not use SCOPE_IDENTITY, instead use Inner Join to get the corresponding value for each record.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 16, 2009 at 10:55 am
Bru Medishetty and Stevie
Guys...that places the ORIGINAL custID into the destination address custID field
I need the destination custID that matches the custID in destination tblCust record
November 16, 2009 at 10:58 am
Larry Brister-297916 (11/16/2009)
My code is below...The new tables getting inserted ito will have some data already and the custID is the pKey...so I HAVE to pick that up each time for the custAddress destination table.
Sorry i was just providing the template for the solution based on the limited info at the time, I will have a look at the code that is posted to see if i can make the solution exact.
But the problem is you are using Primary Keys that have no real meaning, so you need to know what the relationship is between the two tables.
also it makes it a lot easier if you can use the tags in your post.
November 16, 2009 at 10:59 am
Barring other ideas...it seems to me that IF POSSIBLE I need to create a oldCustID column in the destination tables and simply insert the records and do a update in the tblCustAddress where tblCust.oldCustID = tblCustAddress.oldCustID...
But that doesn't really seem right somehow.
November 16, 2009 at 11:03 am
steveb.
I appreciate everything you guys are doing.
I personally didn't set up either end of data.
In the original data tables, I can't see where there was any actual link on the tables.
Probably the scope_identity() was used on the stored procedure insert into customer table and then used as the custID in the address table
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply