Adding Columns to Multiple Tables in SQL

Question

I just created a database and then added a couple of hundred tables with a script like this:

CREATE TABLE CapBond
(
    [timestamp] varchar(50),
    [Reward] varchar(50),
    [Award] varchar(50),
    [Fact] varchar(50)
)

CREATE TABLE Values
(
    [timestamp] varchar(50),
    [Name] varchar(50),
    [Test] varchar(50),
    [Read] varchar(50),
    [Parameters] varchar(50)
)

I realize I forgot to add two columns to each table. One for the PK and one for an FK that points back to a 'master' table.

Is there an easy way to insert columns without having to drop the DB and recreate it? Preferably with the columns inserted as the first two columns in the table?

What database??

Ben Trewern
– Ben Trewern

2020-01-19 00:11:54 +00:00
Commented Jan 19, 2020 at 0:11 — Ben Trewern
– Ben Trewern, Commented Jan 19, 2020 at 0:11

Sándor Jankovics · Accepted Answer · 2020-01-19 00:19:59Z

1

Yes. In mysql you have the alter table command for this purpose. Check out this page for more detailed explanation https://www.sqlservertutorial.net/sql-server-basics/sql-server-alter-table-add-column/ .

And here is the solution for the ordering of the columns https://www.mysqltutorial.org/mysql-add-column/

answered Jan 19, 2020 at 0:19

Sándor Jankovics

8981 gold badge8 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jchornsey Over a year ago

I'm assuming that would be the same for MS SQL? It's been so long since I've done any DB work, I can't remember any commands. I do recall "ALTER TABLE", however, I couldn't begin to remember the syntax. I'll give it a shot. Thanks!

jchornsey Over a year ago

Is it possible to specify the position of the new column?

Sándor Jankovics Over a year ago

For the position I am not sure bit the syntax is similar to the mysql one in mssql as well sqlservertutorial.net/sql-server-basics/…

Collectives™ on Stack Overflow

Adding Columns to Multiple Tables in SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related