Multiple Databases?

Is it possible to retrieveDatafromSQL Database #1and insert it intoSQL Database #2using aStored Procedure?Thanks. If so Can you show example. ThanksYou could do something like this perhaps, I'm not sure on how efficient it would be. This is untested but maybe it will give you enough info to go on, be sure that the dbo has access to the second db you are wanting to insert data into. The other option is a trigger, but you asked how to do it from a SPROC.

CREATE PROCEDURE dbo.YourSPROCName
(
@.Whatever INT --I assume you have a UID your're passing in
)
AS
DECLARE @.Val1 VARCHAR
DECLARE @.Val2 VARCHAR
DECLARE @.Val2 VARCHAR
SELECT @.Val1 =(SELECT Col1 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
SELECT @.Val2 =(SELECT Col2 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
SELECT @.Val2 =(SELECT Col3 FROM Database1.dbo.Table1 WHERE Whatever=@.Whatever)
INSERT INTO Database2.dbo.Table1 (Whatever,Col1,Col2,Col3) VALUES (@.Whatever,@.Val1,@.Val2,@.Val3)
GO

Good luck.|||Thanks PD_Goss

I'm Trying to get it to work.
Question:?
How do you check to see if dbo has access to the second db?

---

Here's what I have so far but its not working. Any Ideas? Thanks.

Create Procedure spDatabaseExport

AS
BEGIN
SELECT MyDatabase1.dbo.Inventory
SELECT QtyInStock
WHERE
ProductID = @.6
GO

BEGIN
DECLARE @.SalesCount INT
INSERT INTO MyDatabase2.dbo.counter3
SELECT SalesCount FROM counter3
WHERE
ID = 2
END
GO|||If you are using dbo it should already have access.

Can you explain what it is you are trying to accomplish?

Here's a quick run down.

AS
 --if you are wanting to query a value from one db and insert it into another this is how:
DECLARE @.SalesCount INT
--Grab what you want
SELECT @.SalesCount = (SELECT SalesCount FROM MyDatabase1.dbo.counter3 WHERE
ID = 2 ) --is this supposed to be a static variable?
--put it in the other table
INSERT INTO MyDatabase2.dbo.counter3 (SalesCount) VALUES (@.SalesCount)
GO

|||Thanks PD_Goss,
What I'm Trying to do is set up a SaleHitCounter to keep track of the amount of individual items sold & store the values into a Different Database. Maybe increment the value by + 1
everytime an item is sold.

I have an OrderItems table Created with the following Columns

uid, OrderID, ProductID, AddressID, Quantity, ProName, Price,

I would like to create a Stored Procedure that's able to update (or) transfer into another Database the total values for each individual items sold. Maybe use the (ProductID, & Quantity, ) Columns? What would be the best way to do this?

TheProductID Column - contains all the Item Numbers e.g. 25, 52, 12, etc.

Is there a way (Stored Procedure) to tap into theProductID & Quantityand somehow get an accumulated total for each individual item - then transfer values to a different database.
But I'm confused on how to go about writing a Stored Procedure to accomplish this. Thanks Again for the help.|||Is the OrderItems table very large. If not, it will be wise idea to run the query directly on OrderItems table as following

select
 ProductID,
 sum (Quantity)
from
 OrderItems
--where -- Need the following 2 lines only if you want to filter
-- ProductID in (25, 52)
group by
 ProductID

If you want to dump this to another database, Run this query on the second database as following

Insert into
 tblCounts
select
 ProductID,
 sum (Quantity)
from
 Database1.dbo.OrderItems
--where -- Need the following 2 lines only if you want to filter
-- ProductID in (25, 52)
group by
 ProductID

Hope this helps

Anil|||This is how I would do it (personal preference I guess). It's important to have constraints (ProductID) so you need to place this after the procedure that updates the OrderItems table, hopefully you have a SPROC that is doing this so you can grab the ProductID parameter for the following code.

--After the Order Items Update
DECLARE @.QtySold INT
SELECT @.QtySold =(SELECT SUM(Quantity) FROM OrderItems WHERE ProductID=@.ProductID)
IF ((SELECT COUNT(ProductID) FROM Database2.dbo.SalesHitCounter WHERE ProductID=@.ProductID)=1)
BEGIN
UPDATE Database2.dbo.SalesHitCounter SET QtySold=@.QtySold WHERE ProductID=@.ProductID
END
ELSE
BEGIN
INSERT INTO Database2.dbo.SalesHitCounter (ProductID,QtySold) VALUES (@.ProductID,@.QtySold)
END

|||Thanks Guys for all the help!

But Database2.dbo.tblCounts is not receiving any inserts from OrderItems (via) Stored Procedure.

Here's where I'm at so far.
TableOrderItems is located inDatabase1
And TabletblCountsis Located inDatabase2

Both Tables are the same (except) tblCounts has nothing in it.
Both tables have the following Columns:(uid, OrderID, ProductID, Quantity, ProductName)
Here is how I have my Stored Procedure set up for now:

CREATE PROCEDURE webcounter4

AS
BEGIN
Select
ProductID,
sum (Quantity)
from
OrderItems
where
ProductID in (1)
group by
ProductID
Insert into
tblCounts
select
ProductID,
sum (Quantity)
from
Database2.dbo.tblCounts
where
ProductID in (1)
group by
ProductID
END
GO

---

Here is my the code from my .ASPX page that fire the Stored Procedure

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@. Page Language="VB" Debug="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SQLClient" %
<script runat="server"
 Sub Page_Load(Source as Object, E as EventArgs)
 Dim objCon As New SQLConnection("server=MyServer\InstanceName;User id=SA;password=Password;database=Database1")
 Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter4", objCon)
 objCon.Open()
 Dim r as SQLDataReader
 r = cmd.ExecuteReader()
 r.read()
 strtblcounts.text = "Sale Hits : " & r.item(0)
 end sub
</script>

---

<BR>
Also I'm not sure how to incorperate this procedure:</B

DECLARE @.Quantity INT
DECLARE @.ProductID INT
DECLARE @.QtySold INT

SELECT @.Quantity =(SELECT SUM(Quantity) FROM OrderItems WHERE
ProductID=@.ProductID)

IF ((SELECT COUNT(PRODUCTID) FROM Database2.dbo.tblcounts WHERE
ProductID=@.ProductID)=1)

BEGIN

UPDATE Database2.dbo.tblCounts SET Quantity=@.Quantity WHERE
ProductID=@.ProductID

END

ELSE

BEGIN

INSERT INTO Database2@..dbo.tblCounts (ProducrID,Quantity) VALUES
(@.ProductID,@.Quantity)
|||This SPROC would insert a count or update the count of each item sold when an order is created, each item having its own row. It would need to be in the SPROC that handles order creation. I assume you are passing in the ProductID so the declaration is not necessary. The tblcounts structure would need to be more like UID, ProductID, QtySold.

--DECLARE @.Quantity INT --Not needed
--DECLARE @.ProductID INT --Not needed
DECLARE @.QtySold INT
--Get the Qty Sold of an item
SELECT @.QtySold =(SELECT SUM(Quantity) FROM OrderItems WHERE
ProductID=@.ProductID)
--Check if the items exists in the table
IF ((SELECT COUNT(ProductID ) FROM Database2.dbo.tblcounts WHERE
ProductID=@.ProductID)=1)
BEGIN
--If a record exists... update it
UPDATE Database2.dbo.tblCounts SET QtySold=@.QtySold WHERE
ProductID=@.ProductID
END
ELSE
BEGIN
--If a record does not exist... insert one
INSERT INTO Database2.dbo.tblCounts (ProductID,QtySold) VALUES
(@.ProductID,@.QtySold)
END

Your webcounter4 SPROC would only need this:

SELECT ProductID,QtySold FROM Database2.dbo.tblcounts WHERE ProductID IN (1)
GROUP BY ProductID

|||Thanks PD_Goss,

I don't have a SPROC that handles the Order Creation, I have aOrderDetails.aspx & OrderDetails.aspx.vb pages I believe that handles Order Creation. If you need to see the OrderDetails.aspx.vb Class file -- I can post it.|||I would definitely use SPROC's for your data work. I started out using inline SQL and when I made the transition it was much easier to manage. You can post the code and I will whip you up a SPROC.|||HI PD_Goss

I was wondering if it would be possible just toemail the class files to you. I have 3 rather large & lenghty class files & I'm not sure which one exactly you need to see --So I'd like to send you all three Class files. But rather than post, could I just Email the files. Thanks.|||Sure, PublicDispAcct@.hotmail.com