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}