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.
| Author |
Topic |
|
ujjaval
Posting Yak Master
108 Posts |
 Posted - 2005-12-28 : 19:11:38
|
| I have a table TableA with following columns:TableA { id int (PK), a bit, b bit, c bit, d bit} Now, I am adding a new column desc in it. So, new table structure will be:TableA { id int (PK), a bit, b bit, c bit, d bit, desc varchar(10)}Now, i want to add values into 'desc' with following rules into existing table. if any of the columns from 'a','b','c' or 'd' are set to 1, all the set columns name should be added to 'desc' column. Meaning if I have a=1, c=1 then for that row my 'desc' value should be 'a,c'. if I have a=1,b=1,d=1 then my 'desc' value should be 'a,b,d'.I've been working on writing SQL for this, but I am unable to figure out the best way to write this. Any suggestions or help??thanks in advance.ujjaval. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-28 : 19:16:51
|
| You should not design a table this way. Why do you want to do this? If the data for desc can be derived from the other columns, then why not just derive them from your application?Tara Kizeraka tduggan |
 |
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2005-12-28 : 19:21:26
|
| later on I required to delete the columns a,b,c and d. That's why I need to transfer data and then I'll delete the columns with bit values stored in it. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-12-28 : 20:05:30
|
| I suspect what you really should be doing is normalzing your table .... storing comma-seperated values in a column is a very bad idea and defeats the purpose of using a relational database. |
|
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2005-12-28 : 20:18:50
|
| I was worried about that. But the problem is, if I keep it as columns, the columns are going to increase in future and later on some columns may be removed. So, I thought the best option would be to store them in a column as comma separated values. What do you think?? Whats the best solution to have a design which can accept more columns.. because simply adding into a table will make it large in structure. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-12-29 : 12:22:12
|
quote:
because simply adding into a table will make it large in structure.
That's not a problem. Some people support databases that are 5TB. The biggest that I support is about 100GB though.Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-29 : 13:56:27
|
quote:
Originally posted by ujjaval
I was worried about that. But the problem is, if I keep it as columns, the columns are going to increase in future and later on some columns may be removed. So, I thought the best option would be to store them in a column as comma separated values. What do you think?? Whats the best solution to have a design which can accept more columns.. because simply adding into a table will make it large in structure.
Bit columns take very little storage. SQL Server can store up to 8 bit columns in a single character. In the space used by your VARCHAR(10), you could store 80 bit columns.It would be easier for people to help you with this design if you explained what it is for. There may be much beeter ways to do what you need.CODO ERGO SUM |
|
|
|
|
|
|
|
|
insert into query for newly added column