Databases

[SqlBeginner]

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.

[justsomeguy]

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.

[SqlBeginner]

They are on different servers. I'm not familiar with "attaching" databases. What situations would you suggest this for?

[justsomeguy]

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.

[davej]

Two different types of databases or not? http://social.msdn.m...b1-720999de7517

Edited March 23, 2013 by davej

[SqlBeginner]

Why would a database only have the tables properties and event_update?

[justsomeguy]

Because those tables are the only ones that got created.

[SqlBeginner]

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

[justsomeguy]

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.

[SqlBeginner]

Would SELECT INTO statement work for this?

[justsomeguy]

As long as you can reference the databases correctly then you can use any query you want.

[SqlBeginner]

Would this query keep the tables updated when there are new fields added into the referenced table?

[justsomeguy]

If you select * then you'll need to keep the table structures consistent. If not, then you need to list the columns you want to select.

[SqlBeginner]

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.

[justsomeguy]

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]

[SqlBeginner]

Perfect! Thanks

[SqlBeginner]

<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>

[justsomeguy]

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.

[SqlBeginner]

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 June 13, 2013 by SqlBeginner

[justsomeguy]

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.