Hi,
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
JasonJason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>[/vbcol]|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>[/vbcol]|||Uri posted example code that you have to modify to suit your needs. The code
is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP
03.phx.gbl...
>
Showing posts with label exception. Show all posts
Showing posts with label exception. Show all posts
Friday, February 24, 2012
alter 10 tables at the same time
Hi,
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
Jason
Jason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
.....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>
|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Uri posted example code that you have to modify to suit your needs. The code is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
>
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
Jason
Jason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
.....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>
|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Uri posted example code that you have to modify to suit your needs. The code is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
>
alter 10 tables at the same time
Hi,
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
JasonJason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
.....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same
>> structure, the only exception is that they have different table names,
>> e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do
>> it in one time, instead of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same
>> structure, the only exception is that they have different table names,
>> e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do
>> it in one time, instead of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>|||Uri posted example code that you have to modify to suit your needs. The code is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
>> Jason
>> Pehaps , you want to create only ONE table for companies.
>> CREATE TABLE Companies
>> (
>> CompanyId INT NOT NULL PRIMARY KEY,
>> CompanyName VARCHAR(50) NOT NULL,
>> .....
>> )
>>
>> So , if you still persist in your option ,use
>>
>> CREATE TABLE T1 (c int) --Replace with the real names
>> CREATE TABLE T2 (c int)
>> CREATE TABLE T3 (c int)
>> GO
>> SELECT
>> N'ALTER TABLE ' +
>> QUOTENAME(TABLE_SCHEMA) +
>> N'.' +
>> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
>> FROM
>> INFORMATION_SCHEMA.TABLES
>> WHERE
>> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
>> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
>> N'.' +
>> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>>
>> GO
>>
>> DROP TABLE T1,T2,T3
>> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
>> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same structure, the only exception is
>> that they have different table names, e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do it in one time, instead
>> of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>>
>
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
JasonJason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
.....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same
>> structure, the only exception is that they have different table names,
>> e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do
>> it in one time, instead of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same
>> structure, the only exception is that they have different table names,
>> e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do
>> it in one time, instead of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>|||Uri posted example code that you have to modify to suit your needs. The code is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
>> Jason
>> Pehaps , you want to create only ONE table for companies.
>> CREATE TABLE Companies
>> (
>> CompanyId INT NOT NULL PRIMARY KEY,
>> CompanyName VARCHAR(50) NOT NULL,
>> .....
>> )
>>
>> So , if you still persist in your option ,use
>>
>> CREATE TABLE T1 (c int) --Replace with the real names
>> CREATE TABLE T2 (c int)
>> CREATE TABLE T3 (c int)
>> GO
>> SELECT
>> N'ALTER TABLE ' +
>> QUOTENAME(TABLE_SCHEMA) +
>> N'.' +
>> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
>> FROM
>> INFORMATION_SCHEMA.TABLES
>> WHERE
>> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
>> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
>> N'.' +
>> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>>
>> GO
>>
>> DROP TABLE T1,T2,T3
>> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
>> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same structure, the only exception is
>> that they have different table names, e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do it in one time, instead
>> of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>>
>
Sunday, February 19, 2012
Allowing an exception to a trigger
I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.
Hello Echo88,
Echo88:
I created an UPDATE trigger on a table - but there one case where I would not the trigger to occur. I mean, in one procedure it may update this table and I would not the trigger to occur a update occurred because of this stored procedure. I could alter my trigger but I am not sure if I would be able to tell which procedure caused it without adding a special column, but if I have to I will.
A short answer is, yes, you would solve this with an additional column.
A slightly more detailed answer would be, check your "architecture" because your access paths here are "imbalanced". I mean, it sounds like sometimes you directly write to the table, sometimes you write through the stored procedure. My advice is go with stored procedures only. Great chance is your trigger's logic here really belongs to its own procedure which you would call instead of straight writing to the table.
Hope this makes sense. -LV
Thanks for the advice. All writes to any table are done through stored procedures, except triggers because I need to access the trigger tables, but I do it for code maintenance reasons - it just simply easier for to pack it up that way.
Anyway, I am not in love with adding columns after my "architecture" has already been established and I found a better to handle the conditional update. I remember that MS SQL handles UPDATES by placing the new row in the INSERTED trigger table and the old row in the DELETED. I simply just compared the two rows and obtained the results I needed.
Subscribe to:
Posts (Atom)