0

I have question about copy tables from a query into other database.

I use this query in SQL Server:

SELECT * FROM information_schema.tables WHERE TABLENAME = '2000'

This query returns tables. And I would like to copy all the returned tables into my other database.

Thank you in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yetispapa
  • 2,174
  • 2
  • 30
  • 52
  • you want to create the same table schema in to other DB or you want to copy data?? – Vijay Hulmani Aug 08 '13 at 11:09
  • I want to filter specific tables from one database and copy the specific tables from the query in my new database – Yetispapa Aug 08 '13 at 11:10
  • 1
    You can create linked server http://msdn.microsoft.com/en-us/library/aa560998.aspx Then you can copy the table using , server.database.schema.table **select * into DbName.dbo.NewTable from LinkedServer.DbName.dbo.OldTable** This will create new table with same schema and structure . – Suraj Singh Aug 08 '13 at 12:43
  • Or you can access linked server using double dots also **SELECT * FROM xxxxxDB..xxxTable** and you can easily copy your data in to new database. – Suraj Singh Aug 08 '13 at 12:50
  • Yes but there are over 600 tables.. when i use the query: select * into DbName.dbo.NewTable from LinkedServer.DbName.dbo.OldTable i must named each NewTable and each OldTable.. – Yetispapa Aug 09 '13 at 06:30

1 Answers1

0

have a look at sp_MSforeachtable and feed your table names into it from information_schema.tables

there is a good example here to get you started sp_MSforeachtable example

Another example that can be modified is here

This could be amended to suit your needs:

Exec sp_MSforeachtable
@command1 = "SELECT COUNT(*) AS [?] FROM ?",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo') 
 and o.name LIKE 'IIN%'"

Replace @command1 with your copy code (something like SELECT * INTO ... and replace @whereand with your filter for your tables or an IN statement if you have a list.

Community
  • 1
  • 1
SteveB
  • 1,474
  • 1
  • 13
  • 21