Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

automatically create insert statements for table data Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2014 5:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 235, Visits: 976
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].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
Post #1579446
Posted Tuesday, June 10, 2014 5:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:39 AM
Points: 3,358, Visits: 7,262
You can read on how to do it in the article linked in my signature.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1579449
Posted Tuesday, June 10, 2014 6:22 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #1579450
Posted Tuesday, June 10, 2014 10:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 1,300, Visits: 3,735
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].[table] ([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);

Post #1579462
Posted Friday, June 13, 2014 11:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 235, Visits: 976
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'
Post #1580665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse