sql query

davidvilson
davidvilson used Ask the Experts™
on
Dear experts,
I need to an sql query moves(append) data from table1 to table2 every day night 12 am..
And also after moving, delete the records from table1.

can some one plz tell me the code of sql query how to handle this?

thanks,
david
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

You can add the contents of table1 to table2, if both tables have the same columns with:

INSERT INTO table 2 SELECT * FROM table1

Then you can remove the records from table1 with either:

TRUNCATE TABLE table1

or

DELETE FROM table1

Regards,
Psyberion.
AneeshDatabase Consultant
Top Expert 2009

Commented:
INSERT INTO Table2
SELECT * FROM  Table1
IF @@ROWCOUNT > 0
  TRUNCATE TABLE Table1
AneeshDatabase Consultant
Top Expert 2009

Commented:
You have o create a SQL Job with the above query and schedule it for 12.00 am every day
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

Commented:
But, table 1 and table 2 are from different databses.. say database1 & database2. the same query works?
Database Consultant
Top Expert 2009
Commented:
yes, you need to provide the qualified name for that  (databasename.dbo.tableName )

INSERT INTO  database1.dbo.Table2
SELECT * FROM  database2.dbo.Table1
IF @@ROWCOUNT > 0
  TRUNCATE TABLE Table1
Also you don't have to specify dbo, you can use:

INSERT INTO database1..table2 SELECT * FROM database2..table2

and then TRUNCATE TABLE database1..table1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial