Geek Answers Handbook

Insert a column between other columns in SQL Server using a script

I am trying to modify a table on a SQL server using a script. I used to always do this through the GUI, but now I need to create a script to do this for clients.

I have a SQL Server database table that looks like this:

Mytable
-------
ColA int NOT NULL
ColB int NOT NULL
ColC int NOT NULL
ColD VARCHAR (100)

The primary key is determined through ColA, ColB and ColC.

I want the SQL script to change the table as follows:

Mytable
-------
ColA int NOT NULL
ColB int NOT NULL
ColX int NOT NULL (new column, default 0 for existing data)
ColC int NOT NULL
ColD VARCHAR (100)

The primary key will now be determined by ColA, ColB, ColX and ColC.

SQL Server. script, . , script , , temp, , , :

ALTER TABLE dbo.Tmp_MyTable ADD CONSTRAINT
    MyTable21792984_ColC_DF DEFAULT ((0)) FOR ColC

, ( 21792984) . , SQL- , .

SQL? , , , / .

. , , , " ". , , ( , , ). , , .

+5
9

SQL Server "" "- . , - , ?

, GUI, script, - script. , , , SQL Server .

+8

, , sql.

, . script , . gui, , , , .

, , , . , . , .

+3

, . , speatare, , , . , . , , . , , , . , , , , Select * , . , zip , - , select * .

+3

CREATE TABLE , , PRIMARY KEY (ColA, ColB, ColC), SQL Server, , - SQL Server , , MyTable21792984_ColC_DF, ( , -, ColC, ).

, , , , - , , , , , , (, ..). CONSTRAINT PK_MyTable PRIMARY KEY (ColA, ColB, ColC) CREATE TABLE . ( GUI, DBA, , : -).

+2

"" "" . , , . , , .

SELECT * FROM ... //, , . , , .

+2

, , . , , , , .

, , , :

ALTER TABLE ADD COLUMN ColX int NOT NULL DEFAULT(0)

script, .

, ( ) , , , , script.

+1

. , , / temp. , .

+1

script. Red-Gate Sql, .

+1

Add a new column to the existing table (add it to the end of all columns) Then create a new table using this table, and in the subquery that is used to build the new table, specify the columns in the order in which you need them in the new table.

Example:

create table person(name varchar2(10),age number);
alter table person add salary number; (now salary is added at last position)
desc person
 name ...
 age  ...
 salary ... so now salary is at the end.

tell me, now I want to create an employee table using this table.

create table employee as select name,salary,age from person;

when you describe an employee table, it is defined as the definition of an AS WELL AS DATA user table.

0
source

More articles:


All Articles