Script To Create INSERT statements

,

The following script allows for the scripting of data into insert statement.
The user only needs to set the tablename and a query for the data they require scripted and run.

Note the script takes into account autonumber primary keys and removes them from the insert statement.

Also Note that this script will only work on tables with a Single Primary key and not on either heaps or compound keys.

This script meets my needs but could probably be improved feel free to send me any updates.

/******************************************************************************
**		File: sqlDump.sql
**
**		Desc: This file allows the scripting of data from a database
**		      To aid migration
**		Auth: Peter Livesey 
**		Date: 14/2/2006 
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**    
*******************************************************************************/




SET ANSI_NULLS ON
SET NOCOUNT ON
DECLARE @Tablename sysname

/*set the table name below to th etable in which the data resides*/
SET @Tablename = 'suppliers'
/*change the following query to query for the dat you require scripted
you may include a where statement if required*/

SELECT * INTO #tabledata  FROM suppliers

DECLARE @tab nvarchar(10)
DECLARE @CRLF char(2)
SET @tab= '  '
SET @CRLF=Char(13)+Char(10)

DECLARE @OutPutStatement varchar (4000)
DECLARE @IDname varchar (100)
DECLARE @SQLstring nvarchar (4000)
DECLARE @FieldNames nvarchar (2000)
declare @CurrentID varchar(1000)

CREATE TABLE #CurrentID (id varchar(1000))
CREATE TABLE #Values (value varchar(1000),type varchar(100))






/*we need the name of the id field for the loop as this is the only field we can guarantee unique*/
SELECT  @IDname = b.name
FROM 
	syscolumns b
INNER JOIN 
	sysindexes si
ON
	b.id = si.id
	and b.colid = si.indid
	
where b.id = OBJECT_ID(@Tablename)
and indid = 1



/*get the fieldnames from the table except autonumbers*/
SELECT  b.name,st.name as stname
into #FieldNames
FROM 
	sysobjects a
INNER JOIN
	syscolumns b
ON
	a.id = b.id
INNER JOIN
	systypes st
ON
	b.xtype = st.xusertype

where a.id = OBJECT_ID(@Tablename)
	and colstat <>1


SET @FieldNames = ''
SELECT  @FieldNames = @FieldNames + b.name + ',' + @CRLF + @tab + @tab 
FROM 
	sysobjects a
INNER JOIN
	syscolumns b
ON
	a.id = b.id
		
WHERE a.name = @Tablename
	and autoval is  null
		
/*remove trailing comma*/
SET @FieldNames = LEFT (@FieldNames,LEN(@FieldNames)-3) 

/*don't go through loop if no key as can't break loop without loop index*/
if (coalesce(@idname,'') !='')
BEGIN 
	/*begin loop*/
	while exists (select '*' from #TableData)
	BEGIN
		/*empty tables for each run through the loop*/
		delete from #CurrentID
		delete from #Values
		/*reset  variable for loop*/
		SET @OutPutStatement = 'INSERT INTO ' + @Tablename  + @CRLF  + @tab +'( ' + @CRLF + @tab + @tab
		/* add fieldnames to insert statement*/
		SET @OutPutStatement= @OutPutStatement + @FieldNames + @CRLF + @tab + ' ) ' + @CRLF +  ' VALUES ' + @CRLF + @tab + ' ( ' 
		
	
		
	
		/* as we have to do this dynamically we will throw 
		   the id into a temprary table and then retrieve into a variable*/
		SET @SQLstring =  'INSERT INTO #CurrentID SELECT  min(CONVERT(varchar(1000),' + @idName + ')) as id FROM #TableData '
		EXEC sp_executesql @SQLstring 
		SELECT @Currentid = id FROM #CurrentID
		
		
		 
		
		
		
		/*place the values into a temp table*/
		SET @SQLstring = ''
		SELECT @SQLstring  =  @SQLstring + @CRLF  + @CRLF +
			
				   ' INSERT INTO  #Values SELECT CONVERT(varchar(1000),' + name + '),''' + stname  + ''' FROM  #Tabledata WHERE '  + @idname  + ' = ''' + CONVERT(varchar(1000),@currentid) +''''
		
				
		FROM #Fieldnames
		
		EXEC sp_executesql @SQLstring 
	
		/* now add the values to the ouput statement*/
		SELECT @OutPutStatement = @OutPutStatement + @CRLF + @tab + @tab +
			CASE type
				WHEN 'char' 		THEN   coalesce('''' + replace(value,'''','''''')+ '''','NULL')  
				WHEN 'varchar' 		THEN   coalesce('''' + replace(value,'''','''''')+ '''','NULL')
				WHEN 'nvarchar'		THEN   coalesce('''' + replace(value,'''','''''')+ '''','NULL')
				WHEN 'nchar'		THEN   coalesce('''' + replace(value,'''','''''')+ '''','NULL')
				WHEN 'text'		THEN   coalesce('''' + replace(value,'''','''''')+ '''','NULL')
				WHEN 'ntext'		THEN   coalesce('''' + replace(value,'''','''''')+ '''','NULL')
				WHEN 'uniqueidentifier'	THEN   coalesce('''' + value + '''','NULL')
				WHEN 'datetime'		THEN   coalesce('''' + value + '''','NULL')
				ELSE  coalesce(value,'NULL')
			END + ','
		FROM #Values
		
		/* remove trailing comma again*/
		SET @OutPutStatement = LEFT (@OutPutStatement,LEN(@OutPutStatement)-1) 
		
		/* finally close the bracket*/
		SET @OutPutStatement = @OutPutStatement + @CRLF + @tab +  ' ) '
		
		/*output the result*/		
		PRINT @OutPutStatement
		
		/*delete statement to help loop*/
		SET @SQLstring = N'DELETE FROM #tabledata WHERE ' + @idName + '= ''' + convert(varchar(1000),@currentid ) + ''''
		exec sp_executesql @SQLstring 
	
	END
END
ELSE

	PRINT ('No Primary Key cannot perform operation')

/*cleanup temp tables*/
drop table #Tabledata
drop table #FieldNames
drop table #CurrentID
drop table #Values

Rate

5 (2)

Share

Share

Rate

5 (2)