Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2005
    Posts
    10

    Unanswered: Populating data from one SQL database to another

    I have a project that entails the following:

    There are two separate SQL Server databases involved that reside on two different servers. One of the depts within our building wants to have building permit data imported from Permit Database on Server "A" to their own database on Server "B".

    I dont think this will be an overly complicated process. There are only a few fields they want populated (5 or 6 tops). This will have to be ran every
    weeknight via some sort of scheduled task in Windows or SQL.

    I was just interested in seeing if anyone has had prior experience working on data transfer like this. I would like to know what would be the best and most efficient way to approach this.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Forget that..just have them set up a linked server on their server to yours and grant them appropriate permissions
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2005
    Posts
    10
    Good idea...but the whole point in them wanting this done is so that the populated fields will display in their GUI program, which references the database. These are front desk clerks who use this, so their knowledge of the DB's and linked servers is non-existent.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, and if they still want the data...you should always offer more than 1 solution, I would create a view of the data they want, then bcp the entire thing out every night to a file in native format, and the robocopy the file over to the other server

    I would then have their server have a job that would truncate a staging table to hold the table, then simply bcp the data in...

    Unless it's HUGE...but it doesn't sound so

    I always look for the simplest methods

    For example what would not be simple, but not rocket science, is to only give them modifications, based on batch window times, and you need to make sure you have add and update timestamps, then they need processes to so inserts/updates and deletes, and you still need to deliver a file, or in this 3 would be better. Then they would need to load 3 tables...then apply them....

    Or ou can set up replication, which is a headache in itself

    Good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by sacwash711
    Good idea...but the whole point in them wanting this done is so that the populated fields will display in their GUI program, which references the database. These are front desk clerks who use this, so their knowledge of the DB's and linked servers is non-existent.
    Well if they know nothing, who's gonna do the work on their server?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2005
    Posts
    10
    You are talking to him...

    I am the one assigned to have these two servers talk to one another and have the data transfer occur nightly w/out them ever knowing what goes down behind the scenes. All they are concerned with is opening the GUI program and see that the data they need has been populated in the permit module.

    Make sense?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, I didn't know you had control over the box

    Get me some DDL of the 2 (it is 2 right) tables

    In any case, create a linked server on your box

    Create an UPDATE Statement in a stored procedure to update the columns

    Code:
        UPDATE link
           SET Col1 = you.Col1
    	 , Col2 = you.Col2
    	 , ect
          FROM <linkedservername>.<database>.<owner>.<TableName> link
    INNER JOIN yourTable you
    	ON link.key = you.key
    And schedule a job at 3:00AM to execute the sproc
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Brett - tell me why a DTS (or is it SSMS on 2k5?) would not be the simplest solution? I understand it's not the "most efficient" but hey - far easier to set up imho.
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I often wonder about similar things... DTS seems to be widely scorned, and I understand that it does have some significant limitations, but it does simple things simply, which is often good enough for me.

    In most cases like this I'd prefer the linked server, just because it is "real time" and has the fewest "moving pieces" in my mind... I'm a replication junkie (I use it for many things that most people never think about), but not everyone is comforatable with replication.

    At least as I understand this problem though, DTS would probably be the first thing that jumped to my mind.

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    I often wonder about similar things... DTS seems to be widely scorned, and I understand that it does have some significant limitations, but it does simple things simply, which is often good enough for me.

    In most cases like this I'd prefer the linked server, just because it is "real time" and has the fewest "moving pieces" in my mind... I'm a replication junkie (I use it for many things that most people never think about), but not everyone is comforatable with replication.

    At least as I understand this problem though, DTS would probably be the first thing that jumped to my mind.

    -PatP
    Because DTS is a hack? And I've seen it be very fickle at times, and because I see people push it to do thing that it aught not be doing (kinda like Access)

    Besides, how hard is it to create a linked server, and a sproc to truncate a table and insert? Better yet, do a drop then an SELECT * INTO

    Maybe not, the drop may cause some compilation problems...ok the, my choise really would be bcp out truncate, bcp in
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •