asked
dynamic sql to retrieve tablenames
Hi, I have some criteria based on which I have a list of table names saved in a what I call "metadata" table. **metadata table** col1 col2 1 tblname1 2 tblname2 etc... the above list of tables reside in 1 databases. the metadata table resides in another database. based on the above table, I need to copy the tables from 1 database to another. so I am trying to write t-sql query such as : SELECT * INTO database2.dbo.@tablename where @tablename = (select tblname from test1.dbo.metadatatable) hope i made sense...any clues?
I would recommend using SSIS, either from the SSMS Import/Export wizard found by right clicking on a database and choosing Tasks and then *Import* or *Export* or by creating a SSIS package to do this work repeatedly. This will ensure the data is moved to a table with the same structure and will be much more reliable than using dynamic TSQL
for SSIS, I will have to manually select tables while exporting them. The number of tables can be as large as 3000 or so. Also, the archive process may need to take number of days as a parameter based on which we need to delete/keep those many no. of days. if there a way to easily do this ?
as I mentioned earlier, there will be a new table created for each day..so daily it will be a manual process of selecting the new table.. how can this be done using SSIS?
12 People are following this question.
