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
LVL 4
LTY83Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database 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
 
morisceCommented:
use the synatx : database..table
eg.

SET @ExistingFamily = (
     SELECT
          ProductFamily_ID
     FROM
          Store2..ProductFamily
     WHERE
          Store2..ProductFamily_ID = @ProductFamilyID )
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.