SQLTeam.com | Weblogs | Forums

How to insert/modify data from another database


#1

I would like to copy some data from database 1, table named customers to database 2, table named customers on the same server/instance. The condition i need to meet is to replace the content in the customers table from "hello" to "bye"

I tried

Insert into database2.dbo.Customers 
SELECT replace(Customers,'hello 1','bye')
  FROM [database1].[dbo].[Customers]

This works well, but my second requirement is to get the highest value from a column in db2 (the db im inserting into) add 1 to it and use that value within my INSERT

So i have

Insert into database2 (Someid, SomeContent) (select max(id)+1 from database2.Column)

This doesnt work error::Syntax is incorrect

So how do i get the value add one to it and insert that value?

My second question is, Im using a Replace statement. What Im doing is replacing some text i.e.

Replace(Customers,'hello 1','bye 1')

Notice how the hello 1 is the string to replace from. The 1 can be any digit going all the way to 1000. How could i replace all digits with the value from the Select Max from above? So it goes from

hello 1 --> hello {Value from Select Max}
hello 20 --> hello {Value from Select Max}
hello 100 --> hello {Value from Select Max}
hello 1000 --> hello {Value from Select Max}


#2

For a database on the same server, use three-part naming:

database.schema.table

Also:

Insert into database2 (Someid, SomeContent) (select max(id)+1 from database2.Column)

The insert specifies two columns, the source just one. You want:

insert into database2.schema,table (id, content)
select max(id) + 1, content
from database2.schema.othertable