Shulou(Shulou.com)05/31 Report--

According to the sql script to modify the database table structure of several solutions, in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Recently, due to the need to make a gadget for the project. Requirements: the customer uses the old library and stores some data. After a period of time, we modify the new library on the basis of the new function proposed by the customer. These changes have a lot of details, including stored procedures, adding tables, changing table field types, and adding fields. Then after we update and test the software ourselves, we need to change the old library on the customer's side, and the data of the old library needs to be saved. Solution 1: I soon came up with the idea of generating a new library script using the features that come with SQL SERVER 08 R2. Change the name of the old library, run the new library script, and then import the old database data into the new database data through the database built-in function. When testing, the amount of data is not large, and the speed is still ideal. But this is still very inconvenient for customers, and a large amount of data is more time-consuming. So boss asked me to make a gadget. Am I going to use the program to do the above, oh my god! So I thought and thought. Solution 2: I will be more detailed, directly manipulate the tables in the database, rename the tables that exist in the database, run a new script to build the table, and then insert the data in the old table. The related SQL statements are as follows: modify the table name: EXEC sp_rename 'oldName',' newName' insert data: insert into newTable (column1,column2,.) Select column1,column2,. From oldTable more related operations: reference database, table, column rename list reference content to facilitate viewing one, change the database name copy code code is as follows: sp_renamedb change the name of the database. The syntax sp_renamedb [@ dbname =] 'old_name', [@ newname =] 'new_name' parameter [@ dbname =] 'old_name' is the current name of the database. Old_name is of type sysname and has no default value. [@ newname =] 'new_name' is the new name of the database. New_name must follow the rules for identifiers. New_name is of type sysname and has no default value. Return code value 0 (successful) or non-zero numeric (failed) permissions only members of the sysadmin and dbcreator fixed server roles can execute sp_renamedb. The following example renames the database accounting to financial. EXEC sp_renamedb 'accounting', 'financial'

2. Change the table name or column name to copy the code as follows: sp_rename [@ objname =] 'object_name', [@ newname =] 'new_name' [, [@ objtype =] 'object_type'] / A. The following example renames the table customers to custs. EXEC sp_rename 'customers', 'custs' B. Rename a column the following example renames the column contact title in table customers to title. EXEC sp_rename 'customers. [contact title]', 'title', 'COLUMN'

Refer to the structure of the replicated table and the SQL statement of the table data to list the references to facilitate viewing 1. Copy the table structure and data to the new table CREATE TABLE new table SELECT * FROM old table 2. Only copy the table structure to the new table CREATE TABLE, new table SELECT * FROM old table WHERE 1, table 2, that is, let the WHERE condition not hold. Method 2: (provided by tianshibao) CREATE TABLE new table LIKE old table 3. Copy the data from the old table to the new table (assuming the two tables have the same structure) INSERT INTO new table SELECT * FROM old table 4. Copy the data from the old table to the new table (assuming the structure of the two tables is different) INSERT INTO the new table (field 1, field 2.) SELECT field 1, field 2. When the old FROM watch thought of this, I found that there seemed to be something missing, er, procedure and constraint. Well, I'll delete and then add select name from sys.procedures select constraint_name first, table_name from information_schema.table_constraints and then delete drop procedure [dbo]. [procedure_name] alter table tableName drop CONSTRAINT [PK_ | FK_ | DF_ | CK_] add something here. For constraint prefixes, refer to SQL constraint prefixes for convenience. List again-primary key constraint PK_ field primary key (field), unique constraint constraint UK_ field unique key (field), default constraint constrint DF_ field default ('default') for field,-check constraint constraint CK_ field check (constraint). Such as: len (field) > 1),-primary foreign key relationship constraint FK_ master table _ slave table foreign (foreign key field) references master table (primary table primary key field) and then read the corresponding script file from the script and execute it (add nonsense: to execute multi-line script files, add'\ n'in CommandText). Solution 3: think about it, why do you have to import data and change the table structure directly? So read the field select column_name,data_type from information_schema.columns where table_name = 'tableName' in the table for the field ALTER TABLE [tableName] ALTER COLUMN [columnName] [int] NOT NULL that exists in the old table, and the field ALTER TABLE [tableName] ADD COLUMN [columnName] [int] NOT NULL that does not exist in the old table, so that you can update the old database structure without importing data, in addition to using some knowledge of ADO.NET. Regular expressions and the like are also used to match the corresponding SQL commands in the script. Of course, there are still some problems in the current scheme, this tool is not universal enough, the redundant columns of the old table are not deleted, and the fault tolerance is not thoughtful enough. Further improvements will be made at a later stage according to the needs of the project.

According to the sql script to modify the database table structure of the solution to which questions are shared here, I hope the above content can be of some help to you, if you still have a lot of doubts have not been solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.