automatically create insert statements for table data

• SQListic

  SSCertifiable

  Points: 6904

  More actions

  June 10, 2014 at 5:09 pm

  #308984

  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', )

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 10, 2014 at 5:58 pm

  #1720607

  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

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  June 10, 2014 at 6:22 pm

  #1720608

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  June 10, 2014 at 10:31 pm

  #1720618

  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);

• SQListic

  SSCertifiable

  Points: 6904

  More actions

  June 13, 2014 at 11:17 am

  #1721596

  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'