Synchronous Bulk-Copy into two table

#1

Hi guys,
in my db i have these three tables

1.Stores 2.Products 3.Parts

their structure is something like :

Stores ----> Products ----> Parts

Stores
----------------
StoreId, StoreName

Products
----------------
ProductId, StoreId, ProductName

Parts
----------------
PartId, ProductId, PartName

now, in my application i wanna to implement a bulk-copy operation so
user can copy products from one store to another one and when a
product copied to new store;

all of it's parts should copy too.
in fact i need a method to insert a Product item in Products table and
synchronously copy it's parts into Parts table and repeat this steps
until all of proucts copied.

how can i do that without cursors or loops ?

Thanks

#2

Khafancoder wrote:

Quote:

in my db i have these three tables

1.Stores 2.Products 3.Parts

their structure is something like :

Stores ----> Products ----> Parts

Stores
----------------
StoreId, StoreName

Products
----------------
ProductId, StoreId, ProductName

Parts
----------------
PartId, ProductId, PartName

now, in my application i wanna to implement a bulk-copy operation so
user can copy products from one store to another one and when a
product copied to new store;

all of it's parts should copy too.
in fact i need a method to insert a Product item in Products table and
synchronously copy it's parts into Parts table and repeat this steps
until all of proucts copied.

how can i do that without cursors or loops ?

Why do you need to do that at all? It seems like you simply need
to do the following:

insert into Products n (ProductId, StoreId, ProductName)
select o.ProductId, @NewStoreId, o.ProductName
from Products o
where o.StoreId = @OldStoreId

#3

So what about parts records ? they need to copied too !

On May 24, 7:10 pm, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:

Quote:

Khafancoder wrote:
in my db i have these three tables

1.Stores 2.Products 3.Parts

their structure is something like :

Stores ----> Products ----> Parts

Stores
----------------
StoreId, StoreName

Products
----------------
ProductId, StoreId, ProductName

Parts
----------------
PartId, ProductId, PartName

now, in my application i wanna to implement a bulk-copy operation so
user can copy products from one store to another one and when a
product copied to new store;

all of it's parts should copy too.
in fact i need a method to insert a Product item in Products table and
synchronously copy it's parts into Parts table and repeat this steps
until all of proucts copied.

how can i do that without cursors or loops ?

Why do you need to do that at all? It seems like you simply need
to do the following:

insert into Products n (ProductId, StoreId, ProductName)
select o.ProductId, @NewStoreId, o.ProductName
from Products o
where o.StoreId = @OldStoreId- Hide quoted text -

- Show quoted text -

#4

Ed Murphy (emurphy42 (AT) socal (DOT) rr.com) writes:

Quote:

Why do you need to do that at all? It seems like you simply need
to do the following:

insert into Products n (ProductId, StoreId, ProductName)
select o.ProductId, @NewStoreId, o.ProductName
from Products o
where o.StoreId = @OldStoreId

I suspect that Khafancoder's problem may be that ProductId is a
unique key and not a key together with StoreID. The latter may or
may not be a better design depending on the business requirements.

I guess Khafancode will tell us it is not. I hope then he also
gives us more information about his tables: which are the keys,
if there are any IDENTITY column. And also which version of SQL Server
he is using.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#5

Thanx,
here is the db schema :
http://i12.tinypic.com/4v5qfbb.gif

(PrimaryKey fields are identity too)
(SQL2005)

On May 25, 12:34 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Ed Murphy (emurph... (AT) socal (DOT) rr.com) writes:
Why do you need to do that at all? It seems like you simply need
to do the following:

insert into Products n (ProductId, StoreId, ProductName)
select o.ProductId, @NewStoreId, o.ProductName
from Products o
where o.StoreId = @OldStoreId

I suspect that Khafancoder's problem may be that ProductId is a
unique key and not a key together with StoreID. The latter may or
may not be a better design depending on the business requirements.

I guess Khafancode will tell us it is not. I hope then he also
gives us more information about his tables: which are the keys,
if there are any IDENTITY column. And also which version of SQL Server
he is using.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

#6

Khafancoder (khafancoder (AT) gmail (DOT) com) writes:

Quote:

Thanx,
here is the db schema :
http://i12.tinypic.com/4v5qfbb.gif

(PrimaryKey fields are identity too)
(SQL2005)

Permit me to remark that the designe does not look good to me. I don't
see why you would copy products and parts from one store to another,
and give them new ids. If you have a product "Widgets", would it not
be the same product in each store?

Had you not used the IDENTITY property, it would have been an easy thing:

BEGIN TRANSACTION

SELECT @newstoreid = colaesce(MAX(storeid), 0) + 1
FROM Stores WITH (UPDLOCK)

INSERT Stores (StoredId, StoreName)
VALUES(@newstoreid, @newstorename)

SELECT @maxprodid = MAX(ProductId) FROM Products
SELECT @minoldprodid = MIN(ProductId)
FROM Stores
WHERE StoredId = @oldstoreid

INSERT Products (ProductId, StoreId, ProductName, ProductDescription)
SELECT @maxprodid + 1 + @minprodid - ProductId, @newstoreid,
ProductName, ProductDescription
FROM Products
WHERE StoreId = @oldstoreid

SELECT @maxpartid = MAX(PartId) FROM Parts
SELECT @minpartid = MIN(Pa.PartId)
FROM Parts Pa
JOIN Products Pr ON Pa.ProductId = Pr.ProductID
WHERE Pr.StoreID = @oldstoreid

INSERT Parts(PartId, ProductId, Partname)
SELECT @maxpartid + 1 + @minpartid - Pa.PartId,
@maxprodid + 1 + @minprodid - Pr.ProductId, Pa.Partname
FROM Parts Pa
JOIN Products Pr ON Pa.ProductId = Pr.ProductID
WHERE Pr.StoreID = @oldstoreid

COMMIT TRANSACTION

Since you use IDENTITY, things become far more cumbersome, and you are
probably best off changing the design so that you are at least not
using identity at all. (I would also prefer a key in Parts that
has ProductId as the first column.)

You could use SET IDENTITY_INSERT and then use the above, but that
requires permissions that a plan user may not have. And it would really
serve to stress that you are using IDENTITY when you shouldn't.

Had PartName and ProductName been known to unique, you could have used
the new OUTPUT clause in SQL 2005, but since they permit NULL, it
does not seem a good idea to use them.

insert into Products n (ProductId, StoreId, ProductName)
select o.ProductId, @NewStoreId, o.ProductName
from Products o
where o.StoreId = @OldStoreId

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#7

Thanks,

Quote:

Permit me to remark that the designe does not look good to me. I don't
see why you would copy products and parts from one store to another,
and give them new ids. If you have a product "Widgets", would it not
be the same product in each store?

because copied products or parts info may needed to be changed by each
owner.
so, i will consider disabling identity insertion.

Thanks for answers

On May 26, 12:57 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Khafancoder (khafanco... (AT) gmail (DOT) com) writes:
Thanx,
here is the db schema :
http://i12.tinypic.com/4v5qfbb.gif

(PrimaryKey fields are identity too)
(SQL2005)

Permit me to remark that the designe does not look good to me. I don't
see why you would copy products and parts from one store to another,
and give them new ids. If you have a product "Widgets", would it not
be the same product in each store?

Had you not used the IDENTITY property, it would have been an easy thing:

BEGIN TRANSACTION

SELECT @newstoreid = colaesce(MAX(storeid), 0) + 1
FROM Stores WITH (UPDLOCK)

INSERT Stores (StoredId, StoreName)
VALUES(@newstoreid, @newstorename)

SELECT @maxprodid = MAX(ProductId) FROM Products
SELECT @minoldprodid = MIN(ProductId)
FROM Stores
WHERE StoredId = @oldstoreid

INSERT Products (ProductId, StoreId, ProductName, ProductDescription)
SELECT @maxprodid + 1 + @minprodid - ProductId, @newstoreid,
ProductName, ProductDescription
FROM Products
WHERE StoreId = @oldstoreid

SELECT @maxpartid = MAX(PartId) FROM Parts
SELECT @minpartid = MIN(Pa.PartId)
FROM Parts Pa
JOIN Products Pr ON Pa.ProductId = Pr.ProductID
WHERE Pr.StoreID = @oldstoreid

INSERT Parts(PartId, ProductId, Partname)
SELECT @maxpartid + 1 + @minpartid - Pa.PartId,
@maxprodid + 1 + @minprodid - Pr.ProductId, Pa.Partname
FROM Parts Pa
JOIN Products Pr ON Pa.ProductId = Pr.ProductID
WHERE Pr.StoreID = @oldstoreid

COMMIT TRANSACTION

Since you use IDENTITY, things become far more cumbersome, and you are
probably best off changing the design so that you are at least not
using identity at all. (I would also prefer a key in Parts that
has ProductId as the first column.)

You could use SET IDENTITY_INSERT and then use the above, but that
requires permissions that a plan user may not have. And it would really
serve to stress that you are using IDENTITY when you shouldn't.

Had PartName and ProductName been known to unique, you could have used
the new OUTPUT clause in SQL 2005, but since they permit NULL, it
does not seem a good idea to use them.

insert into Products n (ProductId, StoreId, ProductName)
select o.ProductId, @NewStoreId, o.ProductName
from Products o
where o.StoreId = @OldStoreId

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

#8

Khafancoder (khafancoder (AT) gmail (DOT) com) writes:

Quote:

because copied products or parts info may needed to be changed by each
owner.
so, i will consider disabling identity insertion.

I still don't think this is a good design. Reasonably, there are
inherit properties with a product that does not depend on the store.

I think you need a new table ProductStores:

CREATE TABLE StoreProducts(StoreId bigint NOT NULL,
ProductId bigint NOT NULL,
CONSTRAINT pk_ProductStores PRIMARY KEY(StoreId,ProductId))

This table can then be augmented with columns that the store owner can
set as he pleases. If needed, you could also have a StoreProductParts,
with (StoreId, ProductId, PartNo) as key and with (StoreId, ProductId)
as foreign key to StoreProducts and (ProductId, PartNo) as foreign key
to Parts. But it seems funny to me that the same product would have
different parts in different stores.

But of course, I don't know what your real case is like. Maybe you are
only using stores, products and parts for the sake of the example?

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#9

Quote:

But of course, I don't know what your real case is like. Maybe you are
only using stores, products and parts for the sake of the example?

exactly!
so i can't change the design and also i can't disable identity
insertion !!

i'm trying to take an alternative way, something lik this :

--disable check constrains

INSERT INTO Parts
(ProductId, PartName, PartDesc)
SELECT
dbo.CopyProduct(ProductId, @DestinationStoreId) , PartName, PartDesc
FROM Parts WHERE StoreId=@StoreId

--enable check constrains

and CopyProduct functions is supposed to copy requested ProductItem
and return it's Id,
but i can't do that in UDF !!

CREATE FUNCTION CopyProduct(@ProductId bigint, @DestinationStoreId
bigint) RETURNS bigint
AS
BEGIN

INSERT INTO Products (StoreId, ProductName)
SELECT @DestinationStoreId, ProductName FROM Products WHERE
ProductId=@ProductId

RETURN SCOPE_IDENTITY()
END

i think it should solve the problem, but because of sqlserver
restriction i can't do that in a function !!

any idea ?

On May 26, 11:50 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Khafancoder (khafanco... (AT) gmail (DOT) com) writes:
because copied products or parts info may needed to be changed by each
owner.
so, i will consider disabling identity insertion.

I still don't think this is a good design. Reasonably, there are
inherit properties with a product that does not depend on the store.

I think you need a new table ProductStores:

CREATE TABLE StoreProducts(StoreId bigint NOT NULL,
ProductId bigint NOT NULL,
CONSTRAINT pk_ProductStores PRIMARY KEY(StoreId,ProductId))

This table can then be augmented with columns that the store owner can
set as he pleases. If needed, you could also have a StoreProductParts,
with (StoreId, ProductId, PartNo) as key and with (StoreId, ProductId)
as foreign key to StoreProducts and (ProductId, PartNo) as foreign key
to Parts. But it seems funny to me that the same product would have
different parts in different stores.

But of course, I don't know what your real case is like. Maybe you are
only using stores, products and parts for the sake of the example?

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

#10

Khafancoder (khafancoder (AT) gmail (DOT) com) writes:

Quote:

But of course, I don't know what your real case is like. Maybe you are
only using stores, products and parts for the sake of the example?

exactly!
so i can't change the design and also i can't disable identity
insertion !!

I presume then that the real tables are more complex than the mock-up
posted.

There is one final question, I will have to ask: in the real Products
table are there any columns beside the IDENTITY column that are unique
within a store? If the answer is yes, then my answer is yes, you
can do it set-based, and if you tell which version of SQL Server you
are using, I can sketch a solution.

If the answer is no, you will have to run a loop and insert the
products one-by-one. You should still be able to copy all parts for
a product in one go, as long as you don't need the part it anywhere.
If this sounds clunky to you, it is because the design is not optimal.

Quote:

and CopyProduct functions is supposed to copy requested ProductItem
and return it's Id,
but i can't do that in UDF !!

CREATE FUNCTION CopyProduct(@ProductId bigint, @DestinationStoreId
bigint) RETURNS bigint
AS
BEGIN

INSERT INTO Products (StoreId, ProductName)
SELECT @DestinationStoreId, ProductName FROM Products WHERE
ProductId=@ProductId

RETURN SCOPE_IDENTITY()
END

Right. A user-defined function cannot change database state, so
that's a non-starter.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx