I am writing a stor proc for a Sql Server 2014 DB that will loop through the tables and then do an insert using each table name returned from the schema select.
I will be inserting the table (and its content) in another database but first dropping the table in the new database.
I know how to pull the tables names using the below select but am unsure on the best way to loop through the tables to do the drop and insert.
Any advise on the best way to do this in a stor proc?
SELECT * FROM DB1.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'