473,385 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Synchronous Bulk-Copy into two table

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

May 24 '07 #1
19 2820
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
May 24 '07 #2
So what about parts records ? they need to copied too !

On May 24, 7:10 pm, Ed Murphy <emurph...@socal.rr.comwrote:
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 -

May 24 '07 #3
Ed Murphy (em*******@socal.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, es****@sommarskog.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
May 24 '07 #4
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...@sommarskog.sewrote:
Ed Murphy (emurph...@socal.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...@sommarskog.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

May 25 '07 #5
Khafancoder (kh*********@gmail.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, es****@sommarskog.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
May 25 '07 #6
Thanks,
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...@sommarskog.sewrote:
Khafancoder (khafanco...@gmail.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...@sommarskog.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

May 26 '07 #7
Khafancoder (kh*********@gmail.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, es****@sommarskog.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
May 26 '07 #8
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...@sommarskog.sewrote:
Khafancoder (khafanco...@gmail.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...@sommarskog.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

May 26 '07 #9
Khafancoder (kh*********@gmail.com) writes:
>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.
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, es****@sommarskog.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
May 26 '07 #10
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.
no, there isn't.
but isn't possible through StoreId & ProductId together ?

and finally, isn't any other alternative to do insert in UDFs ?
or executing an sp in a select statement ?

Thanks

On May 26, 11:26 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
Khafancoder (khafanco...@gmail.com) writes:
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.
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, esq...@sommarskog.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

May 27 '07 #11
Khafancoder (kh*********@gmail.com) writes:
>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.

no, there isn't.
but isn't possible through StoreId & ProductId together ?
I don't know what you have in mind here. The key problem is that
when you insert many rows into a table with an IDENTITY column, and you
need to know the generated IDENTITY value for each row and there is
no other columns that identifies the rows, you will need to insert the
rows one by one. Or override the IDENTITY-generation with SET
IDENTITY_INSERT ON. But the latter that the user has privilege for this
operation; it cannot be granted through ownership chain.
and finally, isn't any other alternative to do insert in UDFs ?
or executing an sp in a select statement ?
It's not really meaningful of talking about an alternative to something
which is completely dead.

What you really should do is to change the database design, because the
current design is the root to your problem.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
May 27 '07 #12
Hi again !

i finally decided to disable identity insertion and do the copy
operation by using temporary Map tables which maps Old Ids and New Ids
so :
--------------------------------------------------------------------
CREATE TABLE #MapProducts (SourceProductId bigint, DestProductId
bigint)

INSERT INTO #MapProducts (SourceProductId, DestProductId)
SELECT ProductId, CASE WHEN
((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
ELSE (SELECT MAX(ProductId) + 1 FROM Products) END
FROM Products WHERE StoreId=@SourceStoreId
--------------------------------------------------------------------
but another problem, this line :
--------------------------------------------------------------------
CASE WHEN ((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
--------------------------------------------------------------------
won't be executed because sql engine calculate COUNT before do the
insert operation.

how could i solve that ?
is it possible to force INSERT command to calculate COUNT after
inserting *each record* ?

Thanks

May 29 '07 #13
Any Idea ?

May 30 '07 #14
Khafancoder (kh*********@gmail.com) writes:
i finally decided to disable identity insertion and do the copy
operation by using temporary Map tables which maps Old Ids and New Ids
so :
--------------------------------------------------------------------
CREATE TABLE #MapProducts (SourceProductId bigint, DestProductId
bigint)

INSERT INTO #MapProducts (SourceProductId, DestProductId)
SELECT ProductId, CASE WHEN
((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
ELSE (SELECT MAX(ProductId) + 1 FROM Products) END
FROM Products WHERE StoreId=@SourceStoreId
--------------------------------------------------------------------
but another problem, this line :
--------------------------------------------------------------------
CASE WHEN ((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
--------------------------------------------------------------------
won't be executed because sql engine calculate COUNT before do the
insert operation.

how could i solve that ?
is it possible to force INSERT command to calculate COUNT after
inserting *each record* ?
That won't fly, as you have noticed. You need to compute the new
id:s from what is given before you start inserting. And this should
not be impossible. Please review this piece of code, which is the
same I posted a couple of days back:

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

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
May 30 '07 #15
Thanks for reply,

i think it won't be a right logic for creating new id s ; or may be i
didn't understand the algorithm correctly

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

Suppose Product Ids are from 1 to 100

so
@maxprodid=100
@minoldprodid = 1

and so, if we gonna to copy a record with id=50 it will be :

@maxprodid + 1 + @minprodid - ProductId = 100 + 1 + 1 - 50
= 52

and 52 is id of an existing product record

Thanks in Advance


On May 31, 12:47 am, Erland Sommarskog <esq...@sommarskog.sewrote:
Khafancoder (khafanco...@gmail.com) writes:
i finally decided to disable identity insertion and do the copy
operation by using temporary Map tables which maps Old Ids and New Ids
so :
--------------------------------------------------------------------
CREATE TABLE #MapProducts (SourceProductId bigint, DestProductId
bigint)
INSERT INTO #MapProducts (SourceProductId, DestProductId)
SELECT ProductId, CASE WHEN
((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
ELSE (SELECT MAX(ProductId) + 1 FROM Products) END
FROM Products WHERE StoreId=@SourceStoreId
--------------------------------------------------------------------
but another problem, this line :
--------------------------------------------------------------------
CASE WHEN ((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
--------------------------------------------------------------------
won't be executed because sql engine calculate COUNT before do the
insert operation.
how could i solve that ?
is it possible to force INSERT command to calculate COUNT after
inserting *each record* ?

That won't fly, as you have noticed. You need to compute the new
id:s from what is given before you start inserting. And this should
not be impossible. Please review this piece of code, which is the
same I posted a couple of days back:

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

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.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- Hide quoted text -

- Show quoted text -

May 30 '07 #16
i solve the problem by using:

SELECT @maxprodid = MAX(ProductId) FROM Products

INSERT Products (ProductId, StoreId, ProductName,
ProductDescription)
SELECT @maxprodid + ROW_NUMBER() OVER (ORDER BY ProductId) ,
@newstoreid,
ProductName, ProductDescription
FROM Products
WHERE StoreId = @oldstoreid
is it correct ?

Thnx
On May 31, 1:31 am, Khafancoder <khafanco...@gmail.comwrote:
Thanks for reply,

i think it won't be a right logic for creating new id s ; or may be i
didn't understand the algorithm correctly
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

Suppose Product Ids are from 1 to 100

so
@maxprodid=100
@minoldprodid = 1

and so, if we gonna to copy a record with id=50 it will be :

@maxprodid + 1 + @minprodid - ProductId = 100 + 1 + 1 - 50
= 52

and 52 is id of an existing product record

Thanks in Advance

On May 31, 12:47 am, Erland Sommarskog <esq...@sommarskog.sewrote:
Khafancoder (khafanco...@gmail.com) writes:
i finally decided to disable identity insertion and do the copy
operation by using temporary Map tables which maps Old Ids and New Ids
so :
--------------------------------------------------------------------
CREATE TABLE #MapProducts (SourceProductId bigint, DestProductId
bigint)
INSERT INTO #MapProducts (SourceProductId, DestProductId)
SELECT ProductId, CASE WHEN
((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
ELSE (SELECT MAX(ProductId) + 1 FROM Products) END
FROM Products WHERE StoreId=@SourceStoreId
--------------------------------------------------------------------
but another problem, this line :
--------------------------------------------------------------------
CASE WHEN ((SELECT COUNT(*) FROM #MapProducts) 0) THEN (SELECT
MAX(DestProductId) + 1 FROM #MapProducts)
--------------------------------------------------------------------
won't be executed because sql engine calculate COUNT before do the
insert operation.
how could i solve that ?
is it possible to force INSERT command to calculate COUNT after
inserting *each record* ?
That won't fly, as you have noticed. You need to compute the new
id:s from what is given before you start inserting. And this should
not be impossible. Please review this piece of code, which is the
same I posted a couple of days back:
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
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.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-Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

May 30 '07 #17
Khafancoder wrote:
i think it won't be a right logic for creating new id s ; or may be i
didn't understand the algorithm correctly
> SELECT @maxprodid + 1 + @minprodid - ProductId,
Presumably this should be @maxprodid - @minprodid + 1 + ProductId
May 31 '07 #18
Khafancoder (kh*********@gmail.com) writes:
i solve the problem by using:

SELECT @maxprodid = MAX(ProductId) FROM Products

INSERT Products (ProductId, StoreId, ProductName,
ProductDescription)
SELECT @maxprodid + ROW_NUMBER() OVER (ORDER BY ProductId) ,
@newstoreid,
ProductName, ProductDescription
FROM Products
WHERE StoreId = @oldstoreid
is it correct ?
Yes, that is a lot better solution that what I posted originally. If
we ignore the fact that it didn't work, I think I wrote it under the
assumption was you were using SQL 2000, and not had access to the
row-number function.

Fairly irrelevant discussion follows: The idea was that if you needed
to copy products 1, 7, 9, 13, and the first available ID was 101, you
would get ids 101, 107, 109 and 113 simply because this would be the easiest
way to solve it on SQL 2000. To get a contiguous series you would have
needed a temp table/table variable with an IDENTITY column.

More relevant final comment: the row_number() function is one of the
absolutely most important additions in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
May 31 '07 #19
Thanks to everyone :)
On Jun 1, 12:41 am, Erland Sommarskog <esq...@sommarskog.sewrote:
Khafancoder (khafanco...@gmail.com) writes:
i solve the problem by using:
SELECT @maxprodid = MAX(ProductId) FROM Products
INSERT Products (ProductId, StoreId, ProductName,
ProductDescription)
SELECT @maxprodid + ROW_NUMBER() OVER (ORDER BY ProductId) ,
@newstoreid,
ProductName, ProductDescription
FROM Products
WHERE StoreId = @oldstoreid
is it correct ?

Yes, that is a lot better solution that what I posted originally. If
we ignore the fact that it didn't work, I think I wrote it under the
assumption was you were using SQL 2000, and not had access to the
row-number function.

Fairly irrelevant discussion follows: The idea was that if you needed
to copy products 1, 7, 9, 13, and the first available ID was 101, you
would get ids 101, 107, 109 and 113 simply because this would be the easiest
way to solve it on SQL 2000. To get a contiguous series you would have
needed a temp table/table variable with an IDENTITY column.

More relevant final comment: the row_number() function is one of the
absolutely most important additions in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.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

Jun 1 '07 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jason | last post by:
What are the technical challenges in getting a local SMTP email server set up on a win3k system or alternatively on a win2k pro local work statation. We are on the verge of acquiring a new win3k...
1
by: Noel | last post by:
Hi, I am a tad confused about if there are any benefits from using asynchronous vs synchronous network communication. As my example, I have been writing a dns lookup stack with a network...
1
by: Chris | last post by:
Hi. I have a ibrary I'm trying to use via javascript within IE. This library uses an asynchronous model where I call into a function and pass it a callback function as one of its arguments. My...
9
by: David | last post by:
Hello I'm testing the XMLHttpRequest object in Firefox and IE. The code below works well in IE and Firefox. It shows "1" when the string is a number and "0" when not. The page aspxTest.aspx only...
2
by: duncansinclair | last post by:
The following code works in IE, but in Forefox (1.0.7 & 1.5) the Asyncronous function (testA) works fine but the Synchronous one (testS) does not. It just doesn't work & I get no errors. ...
3
by: Pro1712 | last post by:
Hi, this may be a stupid question: How can I can call the DoWork-function of a BackgroundWorker synchronous? Or in other words: How can I extend the BackgroundWorker class with a function...
1
by: AECL_DEV | last post by:
Hello Everyone, Ive seen alot of people saying that the best way to AJAX Validate a form is through the submit button, because validation should be synchronous. Im wondering, is there any good...
5
by: HugeBob | last post by:
Hi All, I've got a question about Asynchronous vs Synchronous mode with the XMLHttpRequest object. What are the ramifications of using one mode vs the other? If the script uses Asynchronous...
0
by: ozkhillscovington | last post by:
We have sp's in place that do BULK INSERTS from txt files into the tables. This works fine, however they have asked us to add a field that identifies accounting ctr. The only thing that identifies...
6
by: Simon | last post by:
Hi All, An experiment i'm doing requires requires a synchronous cross-domain request, without using a proxy. I wondered if anyone had any ideas to help me achieve this. Below is what I have...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.