automatically create insert statements for table data

  • Hi,

    How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.

    I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.

    EX : INSERT [dbo].

    ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )

  • You can read on how to do it in the article linked in my signature. 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 1) SSMS Tools Pack. Not free, but you get WAY more than you pay for! Tell Mladen I sent you. 🙂

    2) util_generate_inserts. OLDIE BUT GOODIE, and I still use it regularly.

    http://vyaskn.tripod.com/code/generate_inserts.txt

    http://vyaskn.tripod.com/code.htm

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • SQListic (6/10/2014)


    Hi,

    How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.

    I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.

    EX : INSERT [dbo].

    ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )

    Quick and dirty way of sampling

    😎

    USE tempdb;

    GO

    DECLARE @SAMPLE_SIZE INT = 5;

    DECLARE @SOURCE_TBL NVARCHAR(256) = N'dbo.Sales';

    DECLARE @DESITNATION_TBL NVARCHAR(256) = N'dbo.SalesSample';

    DECLARE @SQL_STR NVARCHAR(MAX) =

    CONCAT ( N'SELECT TOP (',@SAMPLE_SIZE,N') * INTO ',@DESITNATION_TBL,N' FROM ',@SOURCE_TBL,N';');

    PRINT @SQL_STR;

    --EXEC (@SQL_STR);

  • Thanks everyone for the replies

    I found some solution. Thought of sharing

    CREATE procedure [dbo].[INS]

    (

    @Query Varchar(MAX)

    )

    AS

    Set nocount ON

    DEclare @WithStrINdex as INT

    DEclare @WhereStrINdex as INT

    DEclare @INDExtouse as INT

    Declare @SchemaAndTAble VArchar(270)

    Declare @Schema_name varchar(30)

    Declare @Table_name varchar(240)

    declare @Condition Varchar(MAX)

    SET @WithStrINdex=0

    SELECT @WithStrINdex=CHARINDEX('With',@Query )

    , @WhereStrINdex=CHARINDEX('WHERE', @Query)

    IF(@WithStrINdex!=0)

    Select @INDExtouse=@WithStrINdex

    ELSE

    Select @INDExtouse=@WhereStrINdex

    Select @SchemaAndTAble=Left (@Query,@INDExtouse-1)

    select @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))

    Select @Schema_name= Left (@SchemaAndTAble, CharIndex('.',@SchemaAndTAble )-1)

    , @Table_name = SUBSTRING( @SchemaAndTAble , CharIndex('.',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )

    , @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6

    Declare @COLUMNS table (Row_number SmallINT , Column_Name VArchar(Max) )

    Declare @CONDITIONS as varchar(MAX)

    Declare @Total_Rows as SmallINT

    Declare @Counter as SmallINT

    declare @ComaCol as varchar(max)

    select @ComaCol=''

    Set @Counter=1

    set @CONDITIONS=''

    INsert INTO @COLUMNS

    Select Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FRom INformation_schema.columns Where Table_schema=@Schema_name

    And table_name=@Table_name

    and Column_Name not in ('SyncDestination','PendingSyncDestination' ,'SkuID','SaleCreditedto')

    select @Total_Rows= Count(1) FRom @COLUMNS

    Select @Table_name= '['+@Table_name+']'

    Select @Schema_name='['+@Schema_name+']'

    While (@Counter<=@Total_Rows )

    begin

    --PRINT @Counter

    select @ComaCol= @ComaCol+'['+Column_Name+'],'

    FROM @COLUMNS

    Where [Row_number]=@Counter

    select @CONDITIONS=@CONDITIONS+ ' +Case When ['+Column_Name+'] is null then ''Null'' Else ''''''''+

    Replace( Convert(varchar(Max),['+Column_Name+'] ) ,'''''''','''' )

    +'''''''' end+'+''','''

    FROM @COLUMNS

    Where [Row_number]=@Counter

    SET @Counter=@Counter+1

    End

    select @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)

    select @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)

    select @ComaCol= substring (@ComaCol,0, len(@ComaCol) )

    select @CONDITIONS= '''INSERT INTO '+@Schema_name+'.'+@Table_name+ '('+@ComaCol+')' +' Values( '+'''' + '+'+@CONDITIONS

    select @CONDITIONS=@CONDITIONS+'+'+ ''')'''

    Select @CONDITIONS= 'Select '+@CONDITIONS +'FRom ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition

    print(@CONDITIONS)

    Exec(@CONDITIONS)

    Exec [dbo].[INS] 'Person.PersonPhone where 1=1'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply