Jump to content

Recommended Posts

I am a recent grad and have started my career working for an It department. I am on my second project and am having some issues figuring out how to do a task.Our IT department manages many medical facilities. As of right now each facility is tracking their trainings invidually and now my boss (head of IT) wants all facilities to start to have a centralized system in tracking trainings. We purchased train tracks which is a system that pretty much tracks trainings and we have the client server version with web accessability. We ultimately want the HRM database to communicate with the train track database so that it'll automatically put new hires and certain positions into specified trainings. I am a beginner in SQL so my question is what would be the best route to go about getting these two databases to communicate this. I've seen some information about linking and joins but I'm still not 100% sure which would be the best route.

Link to post
Share on other sites

If the databases are on the same server and use the same DBMS then I suppose you can write some stored procedures or something to copy data from one database to the other. If they aren't using the same DBMS on the same server, or the databases aren't able to communicate directly, then you'll need to use another language to pull data from one and add it to the other.

  • Like 1
Link to post
Share on other sites

The destination application might have an API that you can use to move data to it, you'll need to check their documentation. If you want to build something yourself then it would be common to write a script on one server to output a file in a certain format, move that file to the destination server, and have another script there to import it to the database. To support that for our customers they usually use FTP to send us a file in a certain format, and I wrote code to process that file and add it to our database. The code runs on a schedule so there's nothing we need to do other than write the code. I set it up to send them an email if there are any problems with the import, so they can fix the problems and upload another file.

Link to post
Share on other sites
  • 1 month later...
  • 4 weeks later...

This question is in reference to the very first posted question. I have both databases pulled up in SQL Server Management Studio and have identified with tables and fields for both databases I need. I need to bring in 1 table information from database A into a table in database B. I looked at the import wizard but wasn't sure if this was the best route. The amount of data is a couple thousand and I want new information put into table in the specified fields in database A to automatically update in the tables in database B

Link to post
Share on other sites

I've only used those tools a few times, but I think one option is to specify a SQL query to use for the import. You can also use the SQL Query Analyzer to run your own queries, although I'm not sure about the syntax for connecting to a different server.

Link to post
Share on other sites

I was attempting an INSERT INTO statement for this but I'm getting the error invalid column names. Is it because I'm referring two different columns in two different tables in two different databases and I need to do something before I'm able to write a statement between the two first? I'm getting the error for the columns for both tables.

Link to post
Share on other sites

There are some examples here about adding linked servers in management studio and then writing queries to use the linked server: http://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server The syntax is [server].[database].[owner].[table].[field]

Link to post
Share on other sites

<p>One of my co worker said that I should be able to just write a stored procedure and it communicate between the two servers without linking them. This is an example of one I found that I thought would correlate with multiple parameters </p><pre>CREATE PROCEDURE uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULLASSELECT *FROM AdventureWorks.Person.AddressWHERE City = ISNULL(@City,City)AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'GO For my actual situation I modified it to this but still unsure of exactly how to write it out. The first database is Databasetblstaff with the fields </pre><pre>[userID], [FirstName], [LastName], [FacilityID], [Department], [JobCode], [supervisorID], [startDate], [EmailAddress], [Keyword], [EmployeeID], [EmployeeType] put into the database Training with correlating fields C[staffID], [FirstName], [LastName], [LocationID], [DeptID], [JobID], [supervisor], [startDate], , [sSN], [FileNum], [EmpType] REATE PROCEDURE StaffInfo @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULLASSELECT [userID], [FirstName], [LastName], [FacilityID], [Department], [JobCode], [supervisorID], [startDate], [EmailAddress], [Keyword], [EmployeeID], [EmployeeType]FROM Database.dbo.tblstaff WHERE City = ISNULL(@City,City)AND AddressLine1 LIKE '%' + ISNULL(@AddressLine1 ,AddressLine1) + '%'GO</pre>

Link to post
Share on other sites

It looks like that will work if the database is called "Database", the table is "tblstaff", and the owner is "dbo". It's going to look for that on the same server, there's nothing there to tell it to look on a different server.

Link to post
Share on other sites

CREATE PROCEDURE StaffInfo (list name of fields with @ sign in front and separated by commas from database *****.dbo.AC_Users) AS SELECT (List name of fields wanted from *****.dbo.AC_Users) FROM *****.dbo.AC_Users WHERE (list name of fields from second database Train******.dbo.tblstaff that we want corresponding fields inserted into) GO Is this how it should be set up for both databases?

Edited by SqlBeginner
Link to post
Share on other sites

I'm not really sure, I haven't researched this other than what I've posted here. I would expect you would need to specify the server name, database name, owner, and table for each table and some fields in the WHERE clause.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...