 | |
Synchronous Bulk-Copy into two table
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Synchronous Bulk-Copy into two table -
05-27-2007
, 05:45 AM
|
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. |
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... (AT) sommarskog (DOT) se> wrote:
Quote:
|
Khafancoder (khafanco... (AT) gmail (DOT) 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... (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 |
#12
| |||
| |||
|
|
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 ? |
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.
Quote:
|
and finally, isn't any other alternative to do insert in UDFs ? or executing an sp in a select statement ? |
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, 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
#13
| |||
| |||
|
#14
| |||
| |||
|
|
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* ? |
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, 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
#15
| |||
| |||
|
|
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... (AT) sommarskog (DOT) se> wrote:
Quote:
|
Khafancoder (khafanco... (AT) gmail (DOT) 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... (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- Hide quoted text - - Show quoted text - |
#16
| |||
| |||
|
|
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... (AT) sommarskog (DOT) se> wrote: Khafancoder (khafanco... (AT) gmail (DOT) 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... (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-Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#17
| |||
| |||
|
|
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, |
#18
| |||
| |||
|
|
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 ? |
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, 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
#19
| |||
| |||
|
|
Khafancoder (khafanco... (AT) gmail (DOT) 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... (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 |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.