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: Yesterday @ 3:03 PM
Points: 241, Visits: 1,010
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 @ 8:19 AM
Points: 3,639, Visits: 8,155
You can read on how to do it in the article linked in my signature.


Luis C.
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?

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: Today @ 3:02 AM
Points: 4,368, Visits: 6,209
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:41 AM
Points: 2,017, Visits: 5,499
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: Yesterday @ 3:03 PM
Points: 241, Visits: 1,010
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