473,388 Members | 1,068 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,388 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 2318
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: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.