First N Rows Of Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

First N Rows Of Data

Author

Topic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-08 : 16:56:42

This is for you Kristen!!

Instrucions!
-- Change current db (in blue)
-- Change source table (in red)
-- Change sample rows (in red)
-- run to generate insert statements


Use northwind

Declare @dbName varchar(100),
	@tableName varchar(100),
	@seedCnt int
	
Set @tableName = 'customers'
Set @seedCnt = 50

Declare @execStr varchar(8000)

--If there is an image column in the specified table, display warning
if exists(Select * From Information_Schema.columns Where table_Name = @tablename and data_type='image')
	Select 'Image Type not supported'

--If there is an ntext column in the specified table, display warning
if exists(Select * From Information_Schema.columns Where table_Name = @tablename and data_type='ntext')
	Select 'nText Type not supported'


--build column translations
	Select
		@execStr = isnull(@execStr + ' +' + char(13) + char(10),'') + 
			case 
			when data_type in ('uniqueidentifier') 
				then char(9) + '''' + column_name + '='' + isnull('''''''' + convert(varchar(50),' + column_Name + ') + '''''''',''null'') + '', '''
			when data_type in ('char','nchar','varchar', 'nvarchar') 
				then char(9) + '''' + column_name + '='' + isnull('''''''' + replace(' + column_Name + ','''''''','''''''''''') + '''''''',''null'') + '', '''
			when data_type in ('datetime') 
				then char(9) + '''' + column_name + '='' + isnull('''''''' + convert(varchar,' + column_Name + ',121) + '''''''',''null'') + '', '''
			when data_type in ('int','float','decimal','money','bit','smallint','real','bigint') 
				then char(9) + '''' + column_name + '='' + isnull(convert(varchar,' + column_Name + '),''null'') + '', ''' end
	From Information_Schema.columns Where table_Name = @tablename and data_type not in ('image','ntext') 
--	and Ordinal_Position<=1


	--Select @execStr
	Select @execStr=left(@execStr,len(@execStr)-4) + ''''''
	Select @execStr = 'Select top ' + convert(varchar,@seedCnt) + char(13) + char(10) + '''Insert Into #' + replace(@tableName,' ','') + ' Select '' + ' + char(13) + char(10) + @execStr + char(13) + char(10) + 'from [' + @tableName + ']'
	--Select @execStr
	
	Exec(@execStr)

viola!

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-09 : 01:01:31

Well Done Corey
Yours is neat and simple

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-09 : 02:37:21

"This is for you Kristen!!"

Looks good!

I made some changes:

Added some data type missing (tinyint, smallmoney etc.)

CASE statement didn't fail safe (generated NULL which meant that only last remaining columns output!)

Columns in INFORMATION_SCHEMA.COLUMNS return nvarchar, which meant the CASE statement coersced to Nvarchar,
and thus only 4000 characters could be concatenated.
Cast that to varchar instead (which might not be a good idea, but I don't have any chinese column names!)

Added mechanics for tables with lots of columns - it should be able to handle around 600 columns

Changed column / table names for case sensitive database


USE Northwind
GO

DECLARE	@tableName varchar(100),
	@seedCnt int
	
SET @tableName = 'customers'
SET @seedCnt = 50

DECLARE	@execStr0 varchar(8000),
	@execStr1 varchar(8000),
	@execStr2 varchar(8000),
	@execStr3 varchar(8000),
	@execStr4 varchar(8000),
	@execStr5 varchar(8000),
	@execStr6 varchar(8000),
	@execStr7 varchar(8000),
	@execStr8 varchar(8000),
	@execStr9 varchar(8000)

-- Display warning for unsupported types
IF EXISTS(SELECT *
	FROM	INFORMATION_SCHEMA.COLUMNS
	WHERE	    TABLE_NAME = @tablename 
		AND DATA_TYPE NOT IN 
				('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 
				'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 
				'money', 'bit', 'smallint', 'real', 'bigint'))
BEGIN
	SELECT DISTINCT DATA_TYPE + ' Type not supported'
	FROM	INFORMATION_SCHEMA.COLUMNS 
	WHERE	    TABLE_NAME = @tablename 
		AND DATA_TYPE NOT IN 
				('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 
				'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 
				'money', 'bit', 'smallint', 'real', 'bigint')
END
-- Build column translations
SELECT
	@execStr1 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr2 ELSE @execStr1 END,
	@execStr2 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr3 ELSE @execStr2 END,
	@execStr3 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr4 ELSE @execStr3 END,
	@execStr4 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr5 ELSE @execStr4 END,
	@execStr5 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr6 ELSE @execStr5 END,
	@execStr6 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr7 ELSE @execStr6 END,
	@execStr7 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr8 ELSE @execStr7 END,
	@execStr8 = CASE WHEN LEN(@execStr8) > 7500 THEN NULL ELSE @execStr8 END,

	@execStr8 = IsNull(@execStr8 + ' +' + CHAR(13) + CHAR(10),'') +
		CONVERT(varchar(8000),
			CASE 
			WHEN DATA_TYPE IN ('uniqueidentifier') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull('''''''' + CONVERT(varchar(50),' + COLUMN_NAME + ') + '''''''',''null'')+'', '''
			WHEN DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'sysname') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull('''''''' + REPLACE(' + COLUMN_NAME + ','''''''','''''''''''') + '''''''',''null'')+'', '''
			WHEN DATA_TYPE IN ('datetime') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull('''''''' + CONVERT(varchar,' + COLUMN_NAME + ',121)+'''''''',''null'') + '', '''
			WHEN DATA_TYPE IN ('tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 'money', 'bit', 'smallint', 'real', 'bigint') 
				THEN CHAR(9) + '''' + COLUMN_NAME 
					+ '=''+IsNull(CONVERT(varchar,' + COLUMN_NAME + '),''null'')+'', ''' 
			ELSE
				' ** DATA TYPE ' + DATA_TYPE + ' NOT SUPPORTED **'
			END)
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	    TABLE_NAME = @tablename 
	AND DATA_TYPE IN 
			('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname', 
			'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 
			'money', 'bit', 'smallint', 'real', 'bigint')
ORDER BY ORDINAL_POSITION

SELECT	@execStr0 = 'Select top ' + CONVERT(varchar, @seedCnt) + CHAR(13) + CHAR(10) 
			+ '''Insert Into #' + REPLACE(@tableName,' ','') + ' Select '' + ' 
			+ CHAR(13) + CHAR(10)
SELECT	@execStr1 = IsNull(@execStr1+', ', '')
SELECT	@execStr2 = IsNull(@execStr2+', ', '')
SELECT	@execStr3 = IsNull(@execStr3+', ', '')
SELECT	@execStr4 = IsNull(@execStr4+', ', '')
SELECT	@execStr5 = IsNull(@execStr5+', ', '')
SELECT	@execStr6 = IsNull(@execStr6+', ', '')
SELECT	@execStr7 = IsNull(@execStr7+', ', '')
SELECT	@execStr8 = left(@execStr8, len(@execStr8)-4) + ''''''
SELECT	@execStr9 = CHAR(13) + CHAR(10) + 'from [' + @tableName + ']'

-- Comment in for Debug
-- Select @execStr0, @execStr1, @execStr2, @execStr3, @execStr4, @execStr5, @execStr6, @execStr7, @execStr8, @execStr9

EXEC (@execStr0 + @execStr1 + @execStr2 + @execStr3 + @execStr4 
	+ @execStr5 + @execStr6 + @execStr7 + @execStr8 + @execStr9)

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-09 : 05:34:34

>> Added mechanics for tables with lots of columns - it should be able to handle around 600 columns

Are You sure your database is normalized ???

Kristen, have You looked at sp_execresultset (can be used for stuff like this, and you are running out of 8k dynamic space)

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-08-09 : 07:00:20

a varchar(8000), in the above scenario, will allow about 80 columns to be processed. That is actually about the size of our largest table, but I suspected that users wanting to post 10 rows of INSERT statements when asking for help in these Forums might .... errrmmmm ... "have more"

I've forgotten about sp_execresultset, but I don't think it will help here as we need one massive SQL statement, rather than processing of several individual ones.

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-09 : 09:17:28

Wow! I doesn't even really look the same anymore...

Thanks for the additions... and sorry I was too lazy to do it right the first time

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Kristen
Test

22859 Posts

Posted - 2005-08-09 : 14:10:12

"It doesn't even really look the same anymore... "

That's mostly my dyslexia - I find it easier to reformat to what my brain more easily digests!

"sorry I was too lazy to do it right the first time"

Don't be daft ... I didn't see a long queue of people wanting to make the First Draft!

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-09 : 14:31:26

any other obscure scripts you would like to see?? I get bored... or distracted i should say...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Kristen
Test

22859 Posts

Posted - 2005-08-09 : 15:18:05

You are too young to remember "The Last One" - it was an application, written in MBasic, that generated code for an application. It was intended to be the last application you would ever need ...

I went to the press launch, and it was pretty clever ... but some bright spark in the audience said "is calling it The Last One a bit risky - I mean what happens if you come up with an improved solution?"

And sure enough a year later "The Last One Plus" came out ... written in itself I expect

So a script that solves all problems would be handy. Please.

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-09 : 15:29:02

Thats a bit too obscure of a request... but I'll give it a try