Inserting data from one MSSQL DB to a different MSSQL DB

[omeek]

I'm new to MSSQL (Have used MySQL for a little while) and am completely stuck. I have searched the net for days now and am amazed at how little info is out there for MSSQL.

I am selecting multiple rows from one DB and need to insert those rows in to a table in a different DB and I can not figure out how to do it. Im using PHP. I have not been able to find a function for MSSQL similar to mysql_insert_ar ray.

The SELECT output is creating a PHP form with a bunch of rows but I don't know exactly where to start with getting that data in to a table in a different database. In the past, whenever I had to do this it was using tables in the same DB but since this is seperate DB's, I am a bit lost.

Any input would be much appreciated!

[ck9663]

I'm new to MSSQL (Have used MySQL for a little while) and am completely stuck. I have searched the net for days now and am amazed at how little info is out there for MSSQL.

I am selecting multiple rows from one DB and need to insert those rows in to a table in a different DB and I can not figure out how to do it. Im using PHP. I have not been able to find a function for MSSQL similar to mysql_insert_ar ray.

The SELECT output is creating a PHP form with a bunch of rows but I don't know exactly where to start with getting that data in to a table in a different database. In the past, whenever I had to do this it was using tables in the same DB but since this is seperate DB's, I am a bit lost.

Any input would be much appreciated!

Type this in google: sql server INSERT INTO


The syntax you are looking for is:

INSERT INTO DB2.TABLE2
select * from db1..table1 where condition
 

-- CK

[omeek]

Thanks for the info. How do you specify username, password etc.. for multiple databases that have different credentials? I am only familiar with doing a single mssql_connect string to one DB.

I searched Google and, so far, only found how to do a insert/select within the same database. I'll keep searching..and thanks again for your response!

[ck9663]

Why do you need to use different credentials to connect to other DB, if I may ask? Credentials are usually controlled on the server level and rights are filtered down to the db.

There might be some other solutions that we can suggest.

-- CK

[omeek]

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.

[ck9663]

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

[omeek]

Thanks for the suggestions! I think I'll start with #2 and if that fails, go with option 1.

Thanks!
-Oliver

[omeek]

CK:

I was able to use a form to submit the data (results of DB1 query) as an array in PHP then parse through the rows one by one inserting them in to the second DB!

Thanks so much for your help and suggestions!