Posted By

CDIDevs on 12/31/09

Tagged

Versions (?)

Last Edited at 12/31/09 10:51am

Statistics

Viewed 777 times

Favorited by 1 user(s)

Related snippets

Script table as INSERTS

/ Published in: SQL

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

DECLARE @TblName VARCHAR(128)
SET @TblName = 'Categories'
 
	CREATE TABLE #a (id INT IDENTITY (1,1), ColType INT, ColName VARCHAR(128))
 
	INSERT 	#a (ColType, ColName)
	SELECT CASE WHEN DATA_TYPE LIKE '%char%' THEN 1 ELSE 0 END ,
		COLUMN_NAME
	FROM 	information_schema.columns
	WHERE 	TABLE_NAME = @TblName
	ORDER BY ORDINAL_POSITION
 
	IF NOT EXISTS (SELECT * FROM #a)
	BEGIN
		raiserror('No columns found for table %s', 16,-1, @TblName)
		RETURN
	END
 
DECLARE	@id INT ,
	@maxid INT ,
	@cmd1 VARCHAR(7000) ,
	@cmd2 VARCHAR(7000)
 
	SELECT 	@id = 0 ,
		@maxid = MAX(id)
	FROM 	#a
 
	SELECT	@cmd1 = 'select '' insert ' + @TblName + ' ( '
	SELECT	@cmd2 = ' + '' select '' + '
	while @id < @maxid
	BEGIN
		SELECT @id = MIN(id) FROM #a WHERE id > @id
 
		SELECT 	@cmd1 = @cmd1 + ColName + ','
		FROM	#a
		WHERE	id = @id
 
		SELECT @cmd2 = 	@cmd2
				+ ' case when ' + ColName + ' is null '
				+	' then ''null'' '
				+	' else '
				+	  CASE WHEN ColType = 1 THEN  ''''''''' + ' + ColName + ' + ''''''''' ELSE 'convert(varchar(20),' + ColName + ')' END
				+ ' end + '','' + '
		FROM	#a
		WHERE	id = @id
	END
 
 
	SELECT @cmd1 = LEFT(@cmd1,len(@cmd1)-1) + ' ) '' '
	SELECT @cmd2 = LEFT(@cmd2,len(@cmd2)-8) + ' from ' + @tblName
 
	SELECT '/*' + @cmd1 + @cmd2 + '*/'
 
	EXEC (@cmd1 + @cmd2)
	DROP TABLE #a