Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-17 : 10:08:17
|
| Marti writes "(sqlserver 6.5)As per a previous question on sqlteam that I have cut and pasted below, i would like to know if it is possible to insert from one table into another table that is on a different database on a different server ie table "a" on server A to be inserted into table "b" on server B: -Edwin writes "Ok, this may be simple to some, but it sure is a puzzle to me. I want to move a set of data from one table to another table with a similar structure. What I figure was a shady solution: Selecting the data from the source table, then opening the other table and using a loop to populate the destination table. Question: Is there a way I can use just ONE insert statement to do all this? Sort of like incorporating the select statement into the insert statement? If there is one, then please give me some example code." We sure can do this in one statement (but looks like two). --------------------------------------------------------------------------------We'll use the SELECT statement in conjunction with the INSERT statement to make this as easy as possible. Normally, you would code an INSERT statement something like this (using the pubs database):INSERT authors (au_id, au_lname, au_fname, contract)VALUES ('123-45-6789', 'Gates', 'Bill', 1)This will insert one row into the authors table. You could write a program to loop through a set of records and insert them one at a time into another table. SQL Server is designed for set processing. It is optimized to handle groups or sets of records. We can actually replace the VALUES clause with a SELECT statement that will return a set of records. Suppose we have a table called CALIFORNIA_AUTHORS and we want to populate it with the ID and names of the authors from California. The statement would look something like this:INSERT california_authors (au_id, au_lname, au_fname)SELECT au_id, au_lname, au_fnameFROM authorsWHERE State = 'CA'This will take the 15 records with State='CA' and load them into the table california_authors. You can use any type of SELECT statement here. It just has to return a record set that matches the columns in the INSERT statement. It number of columns and datatypes must match (or be implicitly convertable).You can also execute a stored procedure that returns a record set using the EXEC command in place of the SELECT statement. " |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-05-17 : 10:08:17
|
How about:
INSERT ServerA.db1.dbo.california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM ServerB.db2.dbo.authors
WHERE State = 'CA'
Please read up on linked servers. |
 |
|
|
|
|
|
|
|
insert into statement between servers