question

Mandar Alawani avatar image
Mandar Alawani 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?
selectinsertdynamic-sqlmeta-datatablenames
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Fatherjack avatar image
Fatherjack answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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 ?
0 Likes 0 ·
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?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.