Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Creating duplicate records in multiple tables

Author  Topic 

wiltech
Yak Posting Veteran

51 Posts
Posted - 2003-10-06 : 15:27:57
Not really sure how to do this in T-SQL...

I have 3 tables (Package, Points, and PointOptions). How would one create a t-sql command to create a duplicate copy of a package along with it's associated points and point options?

I know how to do it in ADO, and could probably do it using cursors, but there has to be a better way.

Thanks
Tony W


Table defs:
CREATE TABLE [dbo].[Package] (
	[intPackageID] [int] IDENTITY(1,1) NOT NULL ,
	[chrPackageName] [varchar] (20),
     CONSTRAINT [PK_tblPackage] PRIMARY KEY CLUSTERED ([intPackageID])
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Points] (
	[intPointID] [int] IDENTITY(1,1) NOT NULL ,
	[intPackageID] [int] NULL ,
	[chrDescription] [varchar] (30),
     CONSTRAINT [PK_Points] PRIMARY KEY  CLUSTERED ([intPointID]),
     CONSTRAINT [FK_Package_Points] FOREIGN KEY ([intPackageID]) 
          REFERENCES [dbo].[Package] ([intPackageID])
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PointOption] (
	[intPointOptionID] [int] IDENTITY (1, 1) NOT NULL ,
	[intPointID] [int] not null,
	[chrDescription] [varchar] (30),
	[numDefaultQty] [numeric](18, 2) NULL ,
     CONSTRAINT [PK_tblPointOption] PRIMARY KEY CLUSTERED ([intPointOptionID]),
	CONSTRAINT [FK_PointOption_Points] FOREIGN KEY ([intPointID]) 
          REFERENCES [dbo].[Points] ([intPointID]),
) ON [PRIMARY]
and sample data:
SET IDENTITY_INSERT Package ON
INSERT Package (intPackageID, chrPackageName) VALUES (1, 'Test Package #1')
INSERT Package (intPackageID, chrPackageName) VALUES (2, 'Test Package #2')
INSERT Package (intPackageID, chrPackageName) VALUES (3, 'Test Package #3')
SET IDENTITY_INSERT Package OFF

SET IDENTITY_INSERT Points ON
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (1, 1, 'Pkg #1 Point #1')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (2, 1, 'Pkg #1 Point #2')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (3, 1, 'Pkg #1 Point #3')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (4, 2, 'Pkg #2 Point #1')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (5, 2, 'Pkg #2 Point #2')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (6, 2, 'Pkg #2 Point #3')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (7, 3, 'Pkg #3 Point #1')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (8, 3, 'Pkg #3 Point #2')
INSERT Points (intPointID, intPackageID, chrDescription) VALUES (9, 3, 'Pkg #3 Point #3')
SET IDENTITY_INSERT Points OFF

INSERT PointOption VALUES (1, 'PointOption 1-1-1', 5)
INSERT PointOption VALUES (1, 'PointOption 1-1-2', 2)
INSERT PointOption VALUES (1, 'PointOption 1-1-3', 3)
INSERT PointOption VALUES (2, 'PointOption 1-2-1', 5)
INSERT PointOption VALUES (2, 'PointOption 1-2-2', 2)
INSERT PointOption VALUES (2, 'PointOption 1-2-3', 3)
INSERT PointOption VALUES (3, 'PointOption 1-3-1', 5)
INSERT PointOption VALUES (3, 'PointOption 1-3-2', 2)
INSERT PointOption VALUES (3, 'PointOption 1-3-3', 3)
INSERT PointOption VALUES (4, 'PointOption 2-1-1', 5)
INSERT PointOption VALUES (4, 'PointOption 2-1-2', 2)
INSERT PointOption VALUES (4, 'PointOption 2-1-3', 3)
INSERT PointOption VALUES (5, 'PointOption 2-2-1', 5)
INSERT PointOption VALUES (5, 'PointOption 2-2-2', 2)
INSERT PointOption VALUES (5, 'PointOption 2-2-3', 3)
INSERT PointOption VALUES (6, 'PointOption 2-3-1', 5)
INSERT PointOption VALUES (6, 'PointOption 2-3-2', 2)
INSERT PointOption VALUES (6, 'PointOption 2-3-3', 3)
INSERT PointOption VALUES (7, 'PointOption 3-1-1', 5)
INSERT PointOption VALUES (7, 'PointOption 3-1-2', 2)
INSERT PointOption VALUES (7, 'PointOption 3-1-3', 3)
INSERT PointOption VALUES (8, 'PointOption 3-2-1', 5)
INSERT PointOption VALUES (8, 'PointOption 3-2-2', 2)
INSERT PointOption VALUES (8, 'PointOption 3-2-3', 3)
INSERT PointOption VALUES (9, 'PointOption 3-3-1', 5)
INSERT PointOption VALUES (9, 'PointOption 3-3-2', 2)
INSERT PointOption VALUES (9, 'PointOption 3-3-3', 3)
[Edit: fixed the last set of sample data]
[Edit #2: Changed to use identity for the all tables]

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-10-06 : 15:45:45
Why would you want duplicates? What would the data look like after the duplicate is in there?

Tara
Go to Top of Page

wiltech
Yak Posting Veteran

51 Posts
Posted - 2003-10-06 : 15:48:41
quote:
Originally posted by tduggan

Why would you want duplicates? What would the data look like after the duplicate is in there?

Tara
Tara,

I just want a duplicate as a starting point, Once it's been duplicated then the specific changes can be made to make the new set unique.

There are several other fields in those tables that I've omitted in an attempt to not confuse the issue.

Thanks
Tony W
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-10-06 : 15:52:53
What do you mean by duplicate? Do you mean that you want two "Test Package #1"s for example? Then you want all of the data associated with "Test Package #1" in the other tables too?

Tara
Go to Top of Page

wiltech
Yak Posting Veteran

51 Posts
Posted - 2003-10-06 : 16:07:58
Yes, what I'm trying to do is write a stored procedure that will accept a value for intPackageID, duplicate the data in those tables, and return the intPackageID for the newly created package.

Ex: I want a duplicate of Package #2, the following entries are created:

Package:
(4, 'Test Package #2')
Points:
(10, 4, 'Pkg #2 Point #1')
(11, 4, 'Pkg #2 Point #2')
(12, 4, 'Pkg #2 Point #3')
Point Options:
(10, 'PointOption 2-1-1', 5)
(10, 'PointOption 2-1-2', 2)
(10, 'PointOption 2-1-3', 3)
(11, 'PointOption 2-2-1', 5)
(11, 'PointOption 2-2-2', 2)
(11, 'PointOption 2-2-3', 3)
(12, 'PointOption 2-3-1', 5)
(12, 'PointOption 2-3-2', 2)
(12, 'PointOption 2-3-3', 3)

Thanks
Tony W
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-10-06 : 16:16:29
first do this:

insert into Packages (intPackageID, chrPackageName)
select @NewID, chrPackageName
FROM Packages
where intPackageID = @OldID

then do the same for the other two tables. just pass in the @OLdID you want to copy and the @newID to create.

if you like, you can calculated the @newID instead by using something like:

SET @NewID = (SELECT Max(intPackageID) from packages) + 1

or something like that (but beware of multiple user issues with that approach)

- Jeff
Go to Top of Page

wiltech
Yak Posting Veteran

51 Posts
Posted - 2003-10-06 : 16:17:48
quote:
Originally posted by jsmith8858

but beware of multiple user issues with that approach

Jeff,

Yeah that's what I'm trying to avoid

Thanks

Tony W
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-10-06 : 16:26:32
Why aren't you using the IDENTITY on the first two tables? You could then use SCOPE_IDENTITY.

Tara
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-10-06 : 16:38:54
i agree with Tara, then -- definitely use an IDENTITY column.

and, if that's what you are trying to avoid (multi-user issues) it can be helpful to mention that right away !

- Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-10-06 : 16:45:01
An example:

DECLARE @HoldIdentityValue INT

INSERT INTO SomeTable (Column2, Column3, Column4)
SELECT 'A', 1, 'SQLTeam'

SELECT @HoldIdentityValue = SCOPE_IDENTITY

INSERT INTO SomeTable2 (Column2, ColumnA)
SELECT @HoldIdentityValue, 'Tara'

For information about SCOPE_IDENTITY, see SQL Server Books Online. Do not use @@IDENTITY as it can be incorrect.

Tara
Go to Top of Page

wiltech
Yak Posting Veteran

51 Posts
Posted - 2003-10-06 : 16:50:01
quote:
Originally posted by tduggan

An example:

DECLARE @HoldIdentityValue INT

INSERT INTO SomeTable (Column2, Column3, Column4)
SELECT 'A', 1, 'SQLTeam'

SELECT @HoldIdentityValue = SCOPE_IDENTITY

INSERT INTO SomeTable2 (Column2, ColumnA)
SELECT @HoldIdentityValue, 'Tara'

For information about SCOPE_IDENTITY, see SQL Server Books Online. Do not use @@IDENTITY as it can be incorrect.

Tara
Tara,

I'm aware of the IDENTITY and SCOPE_IDENTITY() options I just forgot to include that in those table defs.

The problem the SQL that you posted is that it will get me the 2nd table, but not all the way down to the 3rd.

I think I've finally worked out a solution by looking through some of nr's (Nigel Rivett) posts. I'll post the t-sql in a few minutes.

Thanks for all of your suggestions.
Tony W
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2003-10-06 : 16:51:58
It will work on the 3rd table as well. You just need to put the SCOPE_IDENTITY values into different variables:

DECLARE @HoldFirstIdentityValue INT
DECLARE @HoldSecondIdentityValue INT

INSERT INTO SomeTable (Column2, Column3, Column4)
SELECT 'A', 1, 'SQLTeam'

SELECT @HoldFirstIdentityValue = SCOPE_IDENTITY

INSERT INTO SomeTable2 (Column2, ColumnA)
SELECT @HoldFirstIdentityValue, 'Tara'

SELECT @HoldSecondIdentityValue = SCOPE_IDENTITY

INSERT INTO SomeTable3 (ColumnAA)
SELECT @HoldSecondIdentityValue

Tara
Go to Top of Page

wiltech
Yak Posting Veteran

51 Posts
Posted - 2003-10-06 : 17:04:39
OK, here's my solution:
DECLARE @intPackageID int
DECLARE @intNewPackageID int
DECLARE @intTempPointID int
DECLARE @intNewPointID int

SET @intpackageID = 2
SET @intTempPointID = 0

     INSERT Package 
     SELECT chrPackageName 
     FROM Package
     WHERE intPackageID = @intPackageID

     SET @intNewPackageID = SCOPE_IDENTITY()

     -- Insert the new package
     SELECT *
     INTO #a
     FROM Points
     WHERE intPackageID = @intPackageID

     -- Loop through the points to insert
     WHILE @intTempPointID < (SELECT MAX(intPointID) FROM #a) BEGIN

          -- Get the Next Point to insert
          SET @intTempPointID = (SELECT MIN(intPointID) FROM #a WHERE intPointID > @intTempPointID)
          
          -- Insert the new point
          INSERT Points
          SELECT @intNewPackageID, chrDescription
          FROM Points
          WHERE intPointID = @intTempPointID          

          -- Get the ID of the 
          SET @intNewPointID = SCOPE_IDENTITY()

          -- Insert the point Options
          INSERT PointOption
          SELECT @intNewPointID, chrDescription, numDefaultQty
          FROM PointOption
          WHERE intPointID = @intTempPointID

     END

I've tested it and it seems to work correctly,now I just need to add some error handling and put it in a transaction and I should be good to go.

Thanks
Tony W
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site