April 14, 2009 at 2:40 am
HI, i want to insert the same columns in many tables. Is there any easy way to do that???
April 14, 2009 at 6:07 am
Hi,
would you please be a little more specific on what you're trying to do?
Do you want to add values to the same (existing) columns or do you want to alter multiple tables and add a column with identical parameter?
Also, please show us what you've done so far and what you're struggling with.
Please follow the link below on how to provide sample data:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
April 14, 2009 at 6:31 am
Sorry for the missunderstanding. I was just trying to add columns in some tables. For example:
I have 10 tables: Table1, Table2, Table3....Table10
and they all have only one column, their PK Table1_ID, Table2_ID....
and now i want to add the new column for example testColumn in all the tables, so the new tables will look like this:
Table1 Table2 Table3
----------- ------------ ----------
Table1_ID int, Table2_ID int, Table2_ID int,
testColumn int testColumn int testColumn int ............
Hope this was better 🙂
April 14, 2009 at 10:36 am
Hi
You need to manually write No.Of Alter statements based on No.Of Tables.
There is no Magic;-)
April 14, 2009 at 12:41 pm
Hi Vasco,
I disagree with Vijaya's statement:.
Vijaya Kadiyala (4/14/2009)
HiYou need to manually write No.Of Alter statements based on No.Of Tables.
There is no Magic;-)
Reason:
Assuming the tables to be modified are as provided (structure: base name followed by a number), there is a way to automatically generate a script that will add the columns to all 10 tables:
declare @sql varchar(max)
select @sql=(
select 'alter table test' + cast(tally.n as varchar(2)) + ' add testColumn int; '
from tally where n<11 for xml path (''))
select @sql
exec(@sql)
If the table names cannot be generated like above, I need the "rule" on how to get the table names...
If you don't have a tally table yet, please see the following link for good reasons to have one: http://qa.sqlservercentral.com/articles/TSQL/62867/
April 14, 2009 at 8:26 pm
There are somany ways to write a script.
Couple of points:
1) What if the table names are not same ex: DEPT,EMP,LOCATION etc.
2) What if the table names are having gaps ex: TAB1,TAB3,TAB4,TAB10,TAB12 etc...
3) Why would one will have the table names like TAB1,TAB2,TAB3 etc...Is this a Good Design?
4) What if there are default values to the columns that we want to assign to only some tables.
Thanks -- Vijaya Kadiyala
April 15, 2009 at 12:43 pm
Vijaya Kadiyala (4/14/2009)
There are so many ways to write a script.Couple of points:
1) What if the table names are not same ex: DEPT,EMP,LOCATION etc.
2) What if the table names are having gaps ex: TAB1,TAB3,TAB4,TAB10,TAB12 etc...
3) Why would one will have the table names like TAB1,TAB2,TAB3 etc...Is this a Good Design?
4) What if there are default values to the columns that we want to assign to only some tables.
Thanks -- Vijaya Kadiyala
None of your scenarios does apply to the OP's request. Please see the OP's post on what he was asking for.
Maybe you overlooked my "disclaimer"...
Assuming the tables to be modified are as provided (structure: base name followed by a number),...
.
If the OP would have asked for a solution that works in general, I probably would confirm your arguments. But in this special case I don't.
We should leave it to the OP to decide whether my proposal did help resolve his issues or not.
April 15, 2009 at 3:37 pm
@Lutz
I would do exactly the same way as you did... 😉
But I have a short question:
Do you know how it would be possible to add a CRLF to the end of the rows? If I use either CHAR(13)+CHAR(10) or a direct CRLF witihn the dynamic sql it becomes masked with "& #x0D;" and the linefeed is complete removed. Sure I can use a replace but if you would know a simple solution... 😀
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 16, 2009 at 12:39 am
Florian Reischl (4/15/2009)
Do you know how it would be possible to add a CRLF to the end of the rows? If I use either CHAR(13)+CHAR(10) or a direct CRLF within the dynamic sql it becomes masked with "& #x0D;"
Try using just CHAR(10). If what you want is to have the result of select @sql shown on separate lines in "Results to text" mode, then this will do the job.
April 16, 2009 at 3:25 am
Vladan (4/16/2009)
Florian Reischl (4/15/2009)
Do you know how it would be possible to add a CRLF to the end of the rows? If I use either CHAR(13)+CHAR(10) or a direct CRLF within the dynamic sql it becomes masked with "& #x0D;"
Try using just CHAR(10). If what you want is to have the result of select @sql shown on separate lines in "Results to text" mode, then this will do the job.
Hi Vladan
Thanks! Works fine 🙂
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 16, 2009 at 8:16 am
lmu92 (4/15/2009)
Vijaya Kadiyala (4/14/2009)
There are so many ways to write a script.Couple of points:
1) What if the table names are not same ex: DEPT,EMP,LOCATION etc.
2) What if the table names are having gaps ex: TAB1,TAB3,TAB4,TAB10,TAB12 etc...
3) Why would one will have the table names like TAB1,TAB2,TAB3 etc...Is this a Good Design?
4) What if there are default values to the columns that we want to assign to only some tables.
Thanks -- Vijaya Kadiyala
None of your scenarios does apply to the OP's request. Please see the OP's post on what he was asking for.
Maybe you overlooked my "disclaimer"...
Assuming the tables to be modified are as provided (structure: base name followed by a number),...
.
If the OP would have asked for a solution that works in general, I probably would confirm your arguments. But in this special case I don't.
We should leave it to the OP to decide whether my proposal did help resolve his issues or not.
I Agree with you. Your script is perfect for the question/requirement. As a newbie i woule like to understand why would one will have the table names like TAB1,...TAB10. more for information point of view and to learn something from him.
btw could you please tell me what is "OP"?:-)
April 16, 2009 at 9:43 am
"OP" is the person who started the thread by posting a question - "Original Poster" or something like that.
About the strange names for tables... Often someone wants to store certain data (often aggregated) in tables like "salesJan2009", "salesFeb2009" etc. When they will need to work with these tables and produce results for the whole year 2009 or January results of last 5 years, they will realize how bad idea it was, but probably it will be too late already. And then they will come here and ask how to write a query 😛
Maybe there are some situations where (almost) identical numbered tables are really helpful, but I can't remember any right now.
April 16, 2009 at 10:30 am
Hi Vladan
Thank you for your explanation..
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply