SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 First N Rows Of Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/08/2005 :  16:56:42  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

India
22755 Posts

Posted - 08/09/2005 :  01:01:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Well Done Corey
Yours is neat and simple

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/09/2005 :  02:37:21  Show Profile  Reply with Quote
"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

Edited by - Kristen on 08/09/2005 02:40:03
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 08/09/2005 :  05:34:34  Show Profile  Reply with Quote
>> 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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/09/2005 :  07:00:20  Show Profile  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/09/2005 :  09:17:28  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/09/2005 :  14:10:12  Show Profile  Reply with Quote
"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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/09/2005 :  14:31:26  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/09/2005 :  15:18:05  Show Profile  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/09/2005 :  15:29:02  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Thats a bit too obscure of a request... but I'll give it a try




Select Answer = 42





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."
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/03/2006 :  08:06:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Corey, I think the select statement should have Order by ordinal_position. Isnt it?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000