0

I have a table A which consists more than 7k records,Now i am creating a new table B .In my new table B I need to copy only 1000 records from table A which has more than 7000 records.

No condition applies, it may be any thousand records from 7000 .

Raidri
  • 17,258
  • 9
  • 62
  • 65
Chow.Net
  • 593
  • 6
  • 13
  • 25

3 Answers3

4
INSERT INTO TABLEB(Col1, Col2, .... colN)
    SELECT TOP 1000 Col1, Col2, .... colN FROM TABLEA
Akhil
  • 7,570
  • 1
  • 24
  • 23
  • If you don't specify an explicit `ORDER BY` - which rows are you going to get? There's no *implicit* ordering in a SQL Server table ... – marc_s Jun 13 '13 at 15:23
  • 1
    I've refrained from using any order bys as the question explicitly states `ANY` 1000 rows. Ideally, you'd want to get rows of interest based on a business case, which apparently isn't available in this context. – Akhil Jun 13 '13 at 15:33
2

In SQL Server

SELECT top 1000 *
INTO newTableName
FROM oldTableName;

In MySQL

SELECT *
INTO newTableName
FROM oldTableName Limit 1000;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hisham
  • 455
  • 4
  • 16
  • If you don't specify an explicit `ORDER BY` - which rows are you going to get? There's no *implicit* ordering in a SQL Server or MySQL table ... – marc_s Jun 13 '13 at 15:23
  • yes you are right there isnt implicit ordering in both sql server and mysql acctually his question is not concern with sorted rows he only want to move 1000 rows regardless of sorting. – Hisham Jun 14 '13 at 11:25
0

You can use ROW_NUMBER in a common table expression. 

WITH CTE AS(
   SELECT Col1, Col2, Col3, RN = ROW_NUMBER() OVER (ORDER BY Col1)
   FROM dbo.TableA
)
INSERT INTO dbo.TableB(Col1, Col2, Col3)
    SELECT Col1, Col2, Col3
    FROM CTE
    WHERE RN <= 1000

Then it's easy to change the logic what should be exported. You could change the ORDER BY, apply a PARTITION BY(f.e. to copy duplicates), use multiple ORDER BY comma separated or change the number you want to export.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939