473,288 Members | 1,704 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Inserting data from one MSSQL DB to a different MSSQL DB

5
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_array.

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!
Apr 3 '08 #1
7 2312
ck9663
2,878 Expert 2GB
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_array.

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:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO DB2.TABLE2
  2. select * from db1..table1 where condition
  3.  

-- CK
Apr 3 '08 #2
omeek
5
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!
Apr 3 '08 #3
ck9663
2,878 Expert 2GB
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
Apr 3 '08 #4
omeek
5
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.
Apr 3 '08 #5
ck9663
2,878 Expert 2GB
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
Apr 3 '08 #6
omeek
5
Thanks for the suggestions! I think I'll start with #2 and if that fails, go with option 1.

Thanks!
-Oliver
Apr 6 '08 #7
omeek
5
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!
Apr 6 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Jared Evans | last post by:
I developed a console application that will continually check a message queue to watch for any incoming data that needs to be inserted into MS SQL database. What would be a low-cost method I...
4
by: epaetz | last post by:
I'm doing a bcp out of a table to a file. Some of the fields in a record may have an empty string. When I bcp out to the file and examine it, the fields that have an empty string in the database...
4
by: Ria Van Heerden | last post by:
How do I insert data from one form into two tables in a database? I am working with ASP pages and a MSSQL database Please let me know if one of you have done this before and how this is...
0
by: sherif | last post by:
Hi, I'm making an application using C# and i connect through it to a database made by MSSQL server but i have a problem when inserting or updating in the database in arabic that each arabic...
3
by: darrel | last post by:
I'm using parameterized sql for my Db updates and typically use something like this: objCommand.Parameters.Add("@pageID", System.Data.OleDb.OleDbType.numeric).Value = 24 However, I now need...
2
by: Igal | last post by:
I'm trying to insert a date value into MSSQL, the type of the sql filed is: "smalldatetime" and i'm trying to insert a text Variable that looks like this: "19/02/2006". .... SET update_date='"...
3
by: Robert Johnson | last post by:
Hi all. Created a simple table in my db. 3 colums one is a Int set for autoincrement. Itentity True, seed 1, Incremement 1, null False. The other colums are simple VarChar(50) null false on the...
12
by: Todd Michels | last post by:
Hi all, I am trying to send data from a form and insert it into a MSSQL DB. When I submit the data I get: Warning: mssql_query() : message: The name "Todd" is not permitted in this context....
3
by: krzys | last post by:
I have 2 databases (with different user names and different passwords), and I need to join column_1 from table_A (on first database: database_I) on column_2 from table_B (on second database:...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.