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

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

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

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

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

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

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

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

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




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

22864 Posts

Posted - 2006-11-03 : 08:06:33
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
   

- Advertisement -