| Author |
 Topic  |
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
 Posted - 08/08/2005 : 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
India
22469 Posts |
Posted - 08/09/2005 : 01:01:31
|
Well Done Corey
Yours is neat and simple
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/09/2005 : 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 |
Edited by - Kristen on 08/09/2005 02:40:03 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 08/09/2005 : 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
United Kingdom
22191 Posts |
Posted - 08/09/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/09/2005 : 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
United Kingdom
22191 Posts |
Posted - 08/09/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/09/2005 : 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
United Kingdom
22191 Posts |
Posted - 08/09/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/09/2005 : 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." |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 11/03/2006 : 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 |
|
|
| |
Topic |
|
|
|
First N Rows Of Data
