I have 2 databases on SQL (identical). I want a table from one database to be copied to the other database. They are both the same name and the original table can be overeritten.
Cheers
+1
A:
DELETE FROM `target_database`.`table_name`
INSERT INTO `target_database`.`table_name` SELECT * FROM `source_database`.`table_name`
Hammerite
2010-06-18 11:21:50
Don't forget schema should be 'database'.'schema'.'table'
rdkleine
2010-06-18 11:24:43
TRUNCATE `target_database`.`table_name` would be quicker than DELETE FROM.
Mark Bannister
2010-06-18 11:24:54
DELETE FROM 'SixthSenseUsers','college_survey_questions'INSERT INTO 'SixthSenseUsers','college_survey_questions' SELECT * FROM 'test','college_survey_questions' ----- I put this in but get an error : --- Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'SixthSenseUsers'.Server: Msg 170, Level 15, State 1, Line 2Line 2: Incorrect syntax near 'test'.
Scott Jackson
2010-06-18 11:36:40
@Scott - That looks like a Microsoft SQL Server error message? I think Hammerite's answer is using MySQL backticks. You should always state the product that you are using. You don't need any quotes around the object names in that query.
Martin Smith
2010-06-18 11:39:45
Its SQL server 2000
Scott Jackson
2010-06-18 11:42:57
Try `DELETE FROM SixthSenseUsers.college_survey_questions INSERT INTO SixthSenseUsers.college_survey_questions SELECT * FROM test.college_survey_questions`
Martin Smith
2010-06-18 11:46:02
I'm now getting --- Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'SixthSenseUsers.college_survey_questions' but all the names look correct.
Scott Jackson
2010-06-18 11:54:02
@Scott - I forgot to add a schema into your query. See my updated answer.
Martin Smith
2010-06-18 11:56:54
+1
A:
If you want the tables in sync continuously, you can use Transactional Replication.It will keep on copying the data from one table one db to another table of other DB.
Refer for further setup details over here:
Samiksha
2010-06-18 11:36:38
A:
Assuming no computed columns, Identity columns or FK constraints something like the following should work.
BEGIN TRAN
DELETE FROM SixthSenseUsers.dbo.college_survey_questions
INSERT INTO SixthSenseUsers.dbo.college_survey_questions
SELECT * FROM test.dbo.college_survey_questions
COMMIT
Martin Smith
2010-06-18 11:55:50