• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

Pull Data from one Database into another inside a stored procedure

Here is my procedure,
I'm trying to pull data from Tackleshop database and insert it into Store2 Database - apparently I can't do it the way I have below but i included the code so it may help

/*
      THIS PROCEDURE PULLS PRODUCTS FROM THE MASTER DATABASE INTO THE SLAVE DATABASE
*/
CREATE PROCEDURE jp_ProductFamily_Pull_One
@ProductFamilyID UNIQUEIDENTIFIER
AS

DECLARE @ExistingFamily UNIQUEIDENTIFIER

SET @ExistingFamily = (
      SELECT
            ProductFamily_ID
      FROM
            Store2.ProductFamily
      WHERE
            ProductFamily_ID = @ProductFamilyID )
 
IF @ExistingFamily = "" 


INSERT INTO
      Store2.ProductFamily
(
      ProductFamily_ID ,
      ProductFamily_Name ,
      ProductFamily_Code ,
      ProductFamily_Display ,
      ProductFamily_Layout ,
      ProductFamily_DropShip ,
      ProductFamily_ShortDescription ,
      ProductFamily_LongDescription2 ,
      ProductFamily_Image ,
      ProductFamily_Thumbnail ,
      ProductFamily_BasePrice ,
      ProductFamily_BaseMSRP ,
      ProductFamily_TimeCreated ,
      ProductFamily_TimeUpdated ,
      ProductFamily.Manufacturer_ID ,
      ProductFamily_PriceLow ,
      ProductFamily_PriceHigh ,
      ProductFamily_Notes ,
      ProductFamily_DataFeed ,
      ProductFamily_Taxable
)
SELECT
      ProductFamily_ID ,
      ProductFamily_Name ,
      ProductFamily_Code ,
      ProductFamily_Display ,
      ProductFamily_Layout ,
      ProductFamily_DropShip ,
      ProductFamily_ShortDescription ,
      ProductFamily_LongDescription2 ,
      ProductFamily_Image ,
      ProductFamily_Thumbnail ,
      ProductFamily_BasePrice ,
      ProductFamily_BaseMSRP ,
      ProductFamily_TimeCreated ,
      ProductFamily_TimeUpdated ,
      ProductFamily.Manufacturer_ID ,
      ProductFamily_PriceLow ,
      ProductFamily_PriceHigh ,
      ProductFamily_Notes ,
      ProductFamily_DataFeed ,
      ProductFamily_Taxable
FROM TackleShop.ProductFamily
WHERE ProductFamily_ID = @ProductFamilyID
GO
0
LTY83
Asked:
LTY83
  • 3
  • 3
1 Solution
 
morisceCommented:
use the synatx : database..table
eg.

SET @ExistingFamily = (
     SELECT
          ProductFamily_ID
     FROM
          Store2..ProductFamily
     WHERE
          Store2..ProductFamily_ID = @ProductFamilyID )
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
LTY83,
> Store2.ProductFamily

The above hasto be replaced with either store2.owner.ProductFamily  or Store2..productFamily where owner is the object owner like dbo...

When you are refering the tables from the other database , use the syntax  dbName.ObjectOwner.ObjectName
0
 
LTY83Author Commented:
Hmm

I tried that but I keep getting the error:
Cannot use empty object or column names, Use a single space if necessary.

Thanks

Loren
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LTY83Author Commented:
I also tried the dbname.owner.table and it would give me the error
"invalid object name"
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT @ExistingFamily =     ProductFamily_ID
FROM Store2..ProductFamily
WHERE Store2..ProductFamily_ID = @ProductFamilyID

IF @ExistingFamily IS NULL
 INSERT INTO ...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>I also tried the dbname.owner.table and it would give me the error
You need to replace the dbName  with your database name , owner with the Objectowner, most of the cases this will be dbo, and table with the original tablename

So it will be something like

Store2.dbo.ProductFamily  
0
 
LTY83Author Commented:
Actually, I think I have a diff problem, It appears as if your original solution fixed it

Thanks!

Loren
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now