It is first time that I'm having this problem; I had developed an application which has been deployed on client's computer. Now he requested for some changes in application for which i have to change the tables structure as well as lot of coding too. What i am worry about is that if i make changes to the structure of tables so how i will incorporate the old data (the client has started working upon the application)into this newly structure tables because this newly structured tables will have more or less columns than the previous one.
I ran the alter query and it worked fine for me. But the problem is that If i give this new DB to client, so how i will incorporate the old data into this newly altered DB?
Let me briefly explain my application. I developed a standalone PC application where both DB and application are installed on a single PC.
I would like to set a field to not null which I can do like this: ALTER TABLE table1 ALTER COLUMN field1 BIGINT NOT NULL
My question is there anyway to set this field to NOT NULL without having to specify the data type BIGINT? I'm using dynamic sql and if there was a way then I wouldn't need to go get the data type of the field dynamically and it would save me a step. Thanks.
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
I'm currently stuck with a table that has 350 mil records. Querying this table is insanely slow so I had a better look at existing yearly partitioning. I already managed to partition on a month level which increased the performance/querrying a lot. I did this on the staging table where I used an alter statement to split the 2015 partition by 12 months.
However, in our project we used Data Vault. This means that we have 4 tables (hub, sathub, link, satlink), all carrying 350 mil records. The problem is that altering the partition function does not work. The server cannot handle this action. What the best way is to do this, without having to drop/reload all tables.
I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO
I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance, Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
I have contract table which has built in foreign key constrains. How can I alter this table for delete/ update cascade without recreating the table so whenever studentId/ contactId is modified, the change is effected to the contract table.
Thanks
************************************************** ****** Contract table DDL is
create table contract( contractNum int identity(1,1) primary key, contractDate smalldatetime not null, tuition money not null, studentId char(4) not null foreign key references student (studentId), contactId int not null foreign key references contact (contactId) );
For the length of Column ID is not enough, So I want to alter its length.The alter statement is:
ALTER TABLE student ALTER COLUMN ID CHAR(20)
For the table student is referenced by table score, the alter statement can not alter the column of the table student, and the SQL Server DBMS give the errors.
But, I can manually alter the length of the column ID in SQL SERVER Management Studio. How to alter column length of the master table(student) along with the slave table(score)?
Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern
A customer wants to implement table partitioning on a replicated table.
They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.
So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).
What are his architectural or practical solutions to using table partitioning and replication?
Is there a way to add a column to an existing table and do it all in C#If my query string is as follows how do I execute the query?ALTER TABLE interests ADD COLUMN Swim VARCHAR(1) NOT NULL DEFAULT('n')ThanksMoonWa
I have a table tblABC which exists in 800 databases. I want to run a cursor to turn off the pk field ID indentity permannetly and then to "insert tblABC select * from tblXYZ".
I have tried the "set indentity_insert tblABC on" statement and failed on insert tblABC select * from tblXYZ.
Is there any alter table related t-sql could permannetly turn off the identity?
Hi, is it possible to override the system when adjusting columns. i.e. a system override setting.
For instance if I've got a column that is indexed and I want to adjust it from varchar(50) to varchar(51) it lets me. But if I then try and adjust the column to varchar (49) I get the error below:
Server: Msg 5074, Level 16, State 8, Line 1 The index 'LastName' is dependent on column 'lastname'. Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE ALTER COLUMN lastname failed because one or more objects access this column.
I asked this question on another Forum and was told I had to drop the index first. Just thought I'd ask for a second opinion.
Hi to all! I have a silly problem, but I'm not able to solve it! I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command. ...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance) So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position? ...Thanks a lot! Sergio
p.s.: Sorry for my poor english..I hope you understand my question!
hello, i am trying to add a column to every table in a database, if it (doesn't already exist), with the following code:
declare @name varchar(50) declare c cursor for select name from sys.tables open c fetch next from c into @name while @@FETCH_STATUS = 0 begin if( (select count(*) from sys.columns WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name = @name ) and name='ins_id') = 0 ) begin alter table @name add ins_id int end
fetch next from c into @name end close c deallocate c
and when i run the script i get the error message Incorrect syntax near '@name'. reffering to the alter table statement So, my question is, how can i actually perform the alter table in this case? thank you
Pramod writes "I want to change the primary key field of my database to Identity seed. the table is with data. data should be secured this is the structure '================================= CREATE TABLE [tblStock] ( [StockId] [numeric](18, 0) NOT NULL , [ItemID] [numeric](18, 0) NULL , [Batch] [varchar] (50) NULL , [Qty] [float] NULL , [Prate] [float] NULL , [SRate] [float] NULL , [Mrp] [float] NULL , [PTR] [float] NULL , [batchid] [numeric](18, 0) NULL , [BatchCode] [varchar] (50) NULL , CONSTRAINT [PK_tblStock] PRIMARY KEY NONCLUSTERED ( [StockId] ) ON [PRIMARY] , CONSTRAINT [IX_tblStock] UNIQUE NONCLUSTERED ( [ItemID], [Batch], [Prate] ) ON [PRIMARY] ) ON [PRIMARY] =================================== How do i use alter table with identity seed ALTER TABLE tblStock alter column StockID Numeric IDENTITY (1, 1)
Hi,I'm trying to write a function check if a column exists on a table, andcreates it if it doesn't. The line that the query analyzer is citingis noted. It seems unhappy taking variables in the ALTER TABLEcommand. I can think of a hack around this, but I'm hoping there's abetter way? Muchas gracias in advance :)-DJCode followsCREATE FUNCTION fieldCreator(@TableName varchar(20),@FieldName varchar(20))RETURNS BITASBEGINif (EXISTS (SELECT count (objname)FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',@TableName, 'column', @FieldName)GROUP BY objname))BEGINALTER TABLE @TableName ADD @FieldName int DEFAULT 4 --ERRORS HERE!!!EXEC sp_addextendedproperty 'caption', 'Created by script foranalysis', 'user', dbo, 'table', @TableName, 'column', @FieldNamereturn 1ENDreturn 0ENDGO
I used the MS Access 2000 Upsize wizard to create a Client/serverapplication. After the wizard ran, the original table names had becomethe linked tables and then there were duplicates of the the tablesnamed xxx_local.I realized later that I needed to add some fields to a table andcouldn't do that in Access (couldn't edit the linked tables). So I usedthe Query Analyzer and created a script to add the fields to my table.It looked fine in Query Analyzer, but back in Access the fields wereadded to xxx_local and not xxx, the linked table. My forms and reportsare built on xxx, not xxx_local.How do I do this correctly?Jane
I am trying to insert some new rows in the table tbl_3, but the problem is that I don't know how to go along with the ID column. I want to insert new rows so that it gets appended at the end and new ID is automatically generated. Looks like this had to be done while creating the table. The original table has the id assigned as 'Identity', but I have imported this table to my database and now it does not show that property in the ID column.
The Insert code that I am using is as follows and the tbl_3 has same no. of columns except it also has id. Can I alter table without touching the previous IDs? The ids start at 28118 and ends at 28300.
Code Snippet INSERT INTO [dbo].[tbl_3] (3_name ,3_sort ,3_active ,3_display ,3_date)
select _MANUFACTURERNAME , 1, 1, 'True', GETDATE() from dbo._MANUFACTURERS where upper(_MANUFACTURERNAME) not in ( select Upper(3_name) from dbo.tbl_3 join dbo._MANUFACTURERS on Upper(3_name) = Upper(_MANUFACTURERNAME))
And the error it is returning to me is
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column '3_id', table '.dbo.tbl_3'; column does not allow nulls. INSERT fails.
Hi I need to alter a SQLServer table. I use the following codeDim conn As SqlConnection Dim comm As SqlCommandDim connectionString As String = ConfigurationManager.ConnectionStrings("juizonline").ConnectionString conn = New SqlConnection(connectionString)comm = New SqlCommand("alter table problemas add teste varchar (25))", conn) conn.Open() comm.ExecuteNonQuery() conn.Close() But i get the error: Incorrect syntax near ')'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.Source Error:
Line 85: comm = New SqlCommand("alter table problemas add teste varchar(25))", conn) Line 86: conn.Open() Line 87: comm.ExecuteNonQuery() Line 88: conn.Close() Line 89: End Sub Any help? Thank you
I need to alter a table (expand the column size for varchar(10) to varchar(255)) and the table has 200 million rows. Please suggest me the best and the fastest method to achieve it. The database is on SQL 7.0
