automatically create insert statements for table data

Question

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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 · Answer 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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

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 · Answer 4

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'