Well, the way it is set up, I pull data from a database (db1) my work uses for thier ticketing software then I have that data inserted in to a table in a different database (db2) on the same server. A seperate database is used because if we were to use a table in the database I am puilling from (db1) it would void our support contract. I am doing this for a "shift hand-off" process where we need to import a list of tickets that are assigned to my team.
As for the credentials, since I am using PHP for the SQL interaction, I am under the impression that the seperate credentials are necessary.
I am using PHP for this process. The number of rows being selected will be different each time. Do you think doing some sort of loop to insert the retrieved data would work better? I am only a little familiar with "for" loops in PHP so I am at a bit of a disadvantage. Other than working with different kinds of loops, I have a really good understanding of PHP and mySQL. I had never used SQL Server until now and am a bit surprised at all the differences between the two.
Once again thanks! I appreciate you sharing your knowledge.
Two options you can do:
1. Create a stored proc in db2 to read table from db1 and insert it to the table in db2. You have to allow your stored proc to accept parameters to use for filters and such...On your PHP, call that stored proc with all the parameters. The cons: you're going to use the same credentials to connect to two db's. But it will be transparent and you're not really editing the data from db1 you're just reading it and storing it in db2 for your own process which are in the same server anyway. The pros: it's fast.
2. Am not much of a PHP programmer so you to forgive me. In your PHP, connect to db1. Using a datasource or recordset object/variable get the data you need from the table in db1. Use a loop to go through this recordset and add each row to the table in db2. Pros: you can use different credentials depending on the connection type you use. Cons: slow.
-- CK
5
