0

I want to copy tables from one database / server to another database/server. The requirement is, while copying tables to the destination, I just need only one record from each table(as a sample record).

Have tried data migration options but none of them gives the option to limit the number of records.

Any suggestions will be appreciated. Thanks.

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
PeaceonEarth
  • 1
  • 2
    select top 1 * from tableA ? – Kevin Mee May 16 '18 at 16:44
  • Please [edit] your question to show [the code you have so far](http://whathaveyoutried.com). Most of us here are happy to help you improve your craft, but are less happy acting as short-order unpaid programming staff. Show us your work so far as a [mcve], the result you were expecting and the results you got, and we'll help you figure it out. It may help to re-read [ask]. – Toby Speight May 16 '18 at 16:45
  • Hi, I don't have any query for that. What I tried was to perform it using import/ export or through SSIS. But at there, they have options like either structure or both structure as well as data. what I am looking for is to copy the structure and only one record (single row) from each table. – PeaceonEarth May 16 '18 at 16:49

1 Answers1

2

Not sure that this is answering fully the question but it is a start

SELECT ' SELECT TOP 1 * FROM ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
   JOIN sys.schemas s
     ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

This will generate a select query for each table in your system (user tables).

If you need to excute the statements you can try this (not convenient if you have many tables though):

DECLARE @sql nvarchar(max) = ''
SELECT @sql+=' SELECT TOP 1 * FROM ' + QUOTENAME(s.NAME) + '.' +   QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
   JOIN sys.schemas s
     ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U';

Exec sp_executesql @sql;

Other solution would be to follow this answer

Nerevar
  • 183
  • 9