| Author |
 Topic  |
|
kashyap_sql
Posting Yak Master
India
174 Posts |
 Posted - 10/22/2010 : 03:36:00
|
Hi all
i want to create a table in which i want 100 columns i,e column1, column2, column3, .......column100 how can i get it
thanks in advance |
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
Posted - 10/22/2010 : 04:07:23
|
It is one of the worst design one can come up with.Please read about normalisation.Especially 1NF form.
PBUH
|
 |
|
|
michael.appleton
Posting Yak Master
United Kingdom
159 Posts |
Posted - 10/22/2010 : 04:37:18
|
| No. Read up on database design. |
Edited by - michael.appleton on 10/22/2010 04:38:17 |
|
|
|
vaibhavktiwari83
Aged Yak Warrior
India
837 Posts |
Posted - 10/22/2010 : 04:52:01
|
Please specify your requirement to design such kind of table.
so that anybody can suggest you something.
Vaibhav T
To walk FAST walk ALONE
To walk FAR walk TOGETHER |
|
|
|
kashyap_sql
Posting Yak Master
India
174 Posts |
Posted - 10/22/2010 : 05:40:18
|
i want to create a table in which it should have 100 columns and the sequence of the column's are like column1,column2,column3,.....column100 its a small task asked by my coligue so kindly help me
With Regards
Kashyap M |
|
|
|
kashyap_sql
Posting Yak Master
India
174 Posts |
Posted - 10/22/2010 : 05:41:57
|
quote:
Originally posted by Sachin.Nand
It is one of the worst design one can come up with.Please read about normalisation.Especially 1NF form.
PBUH
quote:
No. Read up on database design
can you provide any link for that which can help me for my requirement
With Regards
Kashyap M |
|
|
|
Sachin.Nand
Flowing Fount of Yak Knowledge
2937 Posts |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3831 Posts |
Posted - 10/22/2010 : 12:35:00
|
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'CREATE TABLE Foo ('
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)
SELECT TOP (100)
@SQL = @SQL + 'Column' + CAST(N AS VARCHAR(3)) + ' INT, '
FROM Tally;
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ')'
SELECT @SQL |
Edited by - Lamprey on 10/22/2010 12:36:07 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/22/2010 : 13:10:21
|
DECLARE @SQL VARCHAR(MAX)
set @sql = ''
SELECT @SQL = @sql +', Col'+convert(varchar(3),number) +' INT'
FROM
master..spt_values
where type = 'p' and number between 0 and 100
select 'CREATE TABLE foo ('+ substring(@sql,2,5000) + ')'
Jim
Everyday I learn something that somebody else already knew |
|
|
|
kashyap_sql
Posting Yak Master
India
174 Posts |
Posted - 10/23/2010 : 00:36:23
|
that's worth a ton thanks lamprey and jimf for your help
With Regards
Kashyap M |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/23/2010 : 03:51:26
|
I have a database where I use SPARSE columns intentionally.
There are at this moment about 8,000 columns. The reason I decided for this design is to avoid EAV model.
There are about 25 million records and each record/customer utilize about 100-150 columns each. So there are many columns with NULL so SPARSE columns does it job greatly.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 10/23/2010 : 04:02:19
|
That would be a good option if you're on 2008 or later
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
kashyap_sql
Posting Yak Master
India
174 Posts |
Posted - 10/23/2010 : 06:00:00
|
i am using sql 2005
With Regards
Kashyap M |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 10/27/2010 : 13:43:59
|
i really cant understand reason for this requirement though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 10/28/2010 : 02:50:12
|
quote:
Originally posted by visakh16
i really cant understand reason for this requirement though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
OP already stated the reason
quote:
its a small task asked by my coligue
Madhivanan
Failing to plan is Planning to fail |
|
|
|
kashyap_sql
Posting Yak Master
India
174 Posts |
Posted - 10/28/2010 : 03:07:55
|
quote:
Originally posted by madhivanan
quote:
Originally posted by visakh16
i really cant understand reason for this requirement though
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
OP already stated the reason
quote:
its a small task asked by my coligue
Madhivanan
Failing to plan is Planning to fail
yup Madhivanan  
With Regards
Kashyap M |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 10/29/2010 : 23:49:40
|
quote:
Originally posted by Peso
I have a database where I use SPARSE columns intentionally.
There are at this moment about 8,000 columns. The reason I decided for this design is to avoid EAV model.
There are about 25 million records and each record/customer utilize about 100-150 columns each. So there are many columns with NULL so SPARSE columns does it job greatly.
N 56°04'39.26"
E 12°55'05.63"
yikes! does SSMS barf if you do this:
select top 10 * from sparseTable
would be a lot of right scrolling!
elsasoft.org |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/30/2010 : 03:59:48
|
You can't do "SELECT *" on sparse columns... 
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 10/30/2010 : 04:02:01
|
What really happens is that there is one über-column (xml) which holds all columns/elements.
SQL Server exposes the named elements as columns for you. That is basically what sparse columns do.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/01/2010 : 16:29:12
|
Visakh, you forgot the third alternative
INSERT Product (AttributeConsolidated)
VALUES ('<ProductAttrib1>1</ProductAttrib1>
<ProductAttrib2>2</ProductAttrib2>
<ProductAttrib3>3</ProductAttrib3>
<ProductAttrib4>4</ProductAttrib4>')
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Topic |
|
how to add 100 columns at a time
