I need to perform this task. say I have a table that the columns are dates.
Table months:
[02-2015],[03-2015], [04-2015],[04-2015]....[02-2016].
When the new month comes I want the table to alter the columns automatically instead of manual how we do it now. The new Month table should delete [02-2015] and replace it with [03-2015].
table months:
[03-2015],[04-2015],[05-2015],[06-2015]....[03-2016].
I am dropping 60 out of 133 columns on a user table, but am finding that the spaceused by the table ( as shown by sysindexes.dpages ) is not being freed up.
I have dropped and recreated all indexes on the table, with no effect on the dpages value, as well as running dbcc updateusage for the table.
Is it not possible to free up the space used by using this method ?
Is recreating and repopulating the table with only the required columns the correct / only way to do this ?
thanks,
manoj
How do I alter multiple columns with one SQL statement?
I've tried :
ALTER TABLE epcs_benefit_plan ALTER COLUMN
abc1 varchar(3) not null,
abc2 varchar(3) not null
I am having problem when performing the alter table switch. Both tables are identical and have pk.
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresults' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part' .
CONSTRAINT [PKIDX_testSummary] PRIMARY KEY CLUSTERED
( [testresult_id] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON TResultsScheme (testresult_id) ) ON TResultsScheme (testresult_id)
I have performed the function on other tables successfully, but this is the first that has an identity column involved. Is there anything special that needs to be done?
[testresult_id] [int] IDENTITY(1,1) NOT NULL,
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
View 1 Replies View RelatedNew to SSIS...
I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?
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
View 2 Replies View RelatedI 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?
my code:
Alter table customers alter column age
Hello,
I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.
I have succesfully edited a single column with the following code:
USE THCIC
ALTER TABLE PudfTest
ALTER COLUMN
DISCHARGE VARCHAR(6) NULL
However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.
Any help would be greatly appreciated.
Thanks!
Take a Dataflow Task, with an OLEDB Source Component and an OLEDB Destination Component in it. The Source component's Source is stored in a SQLQuery variable, and the Destination component's Destination is stored in a TableName variable. The Dataflow Task is put into an For Loop container.
I just want to do one thing:
In the For Loop, everytime it send a new value to the SQLQuery variable and the TableName variable, then I could use it to transfer many tables in a same logic.
My question is: Really, I could send new values to the variables and make it go. But everytime it says "The external metadata column collection is out of synchronization with the data source columns." Because the external metadata was recorded and not be change automatically everytime of Loop.
How to update the source columns'metadata automatically?
This does not work, incorrect syntax... Please help: ALTER TABLE dbo.classifieds_HotelAds ALTER COLUMN (
HasValetParking varchar(1) NULL,
HasContinentalBreakfast varchar(1) NULL,
HasInRoomMovies varchar(1) NULL,
HasSauna varchar(1) NULL,
HasWhirlpool varchar(1) NULL,
HasVoiceMail varchar(1) NULL,
Has24HourSecurity varchar(1) NULL,
HasParkingGarage varchar(1) NULL,
HasElectronicRoomKeys varchar(1) NULL,
HasCoffeeTeaMaker varchar(1) NULL,
HasSafe varchar(1) NULL,
HasVideoCheckOut varchar(1) NULL,
HasRestrictedAccess varchar(1) NULL,
HasInteriorRoomEntrance varchar(1) NULL,
HasExteriorRoomEntrance varchar(1) NULL,
HasCombination varchar(1) NULL,
HasFitnessFacility varchar(1) NULL,
HasGameRoom varchar(1) NULL,
HasTennisCourt varchar(1) NULL,
HasGolfCourse varchar(1) NULL,
HasInHouseDining varchar(1) NULL,
HasInHouseBar varchar(1) NULL,
HasHandicapAccessible varchar(1) NULL,
HasChildrenAllowed varchar(1) NULL,
HasPetsAllowed varchar(1) NULL,
HasTVInRoom varchar(1) NULL,
HasDataPorts varchar(1) NULL,
HasMeetingRooms varchar(1) NULL,
HasBusinessCenter varchar(1) NULL,
HasDryCleaning varchar(1) NULL,
HasIndoorPool varchar(1) NULL,
HasOutdoorPool varchar(1) NULL,
HasNonSmokingRooms varchar(1) NULL,
HasAirportTransportation varchar(1) NULL,
HasAirconditioning varchar(1) NULL,
HasClothingAiron varchar(1) NULL,
HasWakeupService varchar(1) NULL,
HasMiniBarInRoom varchar(1) NULL,
HasRoomService varchar(1) NULL,
HasHairDryer varchar(1) NULL,
HasCarRentDesk varchar(1) NULL,
HasFamilyRooms varchar(1) NULL,
HasKitchen varchar(1) NULL,
HasMap varchar(1) NULL,
HasWiFi varchar(1) NULL,
GDSChainCode nvarvarchar(1) NULL,
GDSChainCodeName nvarchar(50) NULL,
DestinationID nvarchar(50) NULL,
DrivingDirections nvarchar(MAX) NULL,
NearbyAttractions nvarchar(MAX) NULL,
IANHotelID int NULL,HasClothingIron bit NULL
)
GO
COMMIT
Hi all
ALTER TABLE <Table_Name> ALTER COLUMN <column_name> <Data_type>
above query alters the sigle column but how to alter multiple columns at time
saivyshnav
Junior DBA
I would like to alter view to add columns. My base table is populated by application and the base table name is always change. Is there any way to add some extra columns to this view.
One way is to create a sp to add columns and use the view inside the sp. But I would like to know is there any way to alter the view.
I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.
Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .
The view is called TEMP_EXPORT_1
I can either use the code below to return a list of columns that I want removed:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'
Or I can use the code below to return the list of columns that I want to keep:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'
Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...
Therefore I tried the following but I'm not sure of the syntax:
ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1
Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.
Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
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;
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies View RelatedHi all,We recently upsized two Microsoft Access Databases to SQL. We'reusing an ADP (2002) as the front end.All the conversion issues have been resolved, except for one:Whenever we insert a record into a table, the table isn't sorted byprimary key like I would expect. Instead, the record can be found atthe end of the table. This makes finding a particular record(especially as time goes on) very difficult.I've tried eliminating all indexes except for the primary key, andalso writing AFTER INSERT triggers, but the table still does not sortcorrectly.Any suggestions would be greatly appreciated!Matt
View 4 Replies View RelatedI have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
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.
View 4 Replies View RelatedI 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)
);
In SQL Server 2005,here are two tables, created by the following SQL Statements:
CREATE TABLE student(
ID CHAR(6) PRIMARY KEY,
NAME VARCHAR(10),
AGE INT
);
CREATE TABLE score(
ID CHAR(6) PRIMARY KEY,
SCORE INT,
FOREIGN KEY(ID) REFERENCES student(ID)
);
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)?
Thanks!
which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?
View 2 Replies View RelatedHello!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
View 1 Replies View RelatedA 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?
thx
Does anyone know if the results from a query run in QA automatically go into a tempoary table? For instance if ive just run a query that took 5 minutes but it was a normal select query and after it ran i realised i wanted to query the results could i run a simple command to mke a copy of the result set rather than run the original query into a temp table that would then take a few minutes again?
View 1 Replies View Relatedhi,
i have 2 tables where i require an unique ID over both. I googled a bit and discovered that i need to create an own table for this which holds the last value (because mssql unfortunately does not support sequences).
i did this but my problem now is that i want to automatically set the id of my tables with a trigger on INSERT.
hope this is possible .. or anyone have other suggestions?
thanks a lot!
Hi guys,
I am new to SQL server, but I need to develop a "big" application with it. Here's my application requirements:
1)Create a table, say TableA, in SQL server 7 or 2000 database,say DB1, based on another Oracle database,say DB2, on a remote server. Update TableA based on DB2 every day in some specific time.
2) If new records are inserted into the TableA,some other tables needed to update in DB1. At the same time, an e-mail will send to three receipts.
I have no idea of which function or method I can use to fulfill the requirements. Can any experts here give me some advice, detail explanation would be grateful.
I appreciate any help you provide.
Dogli
I am very well versed on manipulating data using passthrough queries through access or the command object in ADO/VBA but this problem requires a sql server side solution and I have been assigned the task
Scenario
Access via an ODBC link cannot view Sql table data that uses a BigInt as a primary key. Access can update the data, insert data ands delete data to these tables - but viewing (by design) returns the actual count of the rows but each field is populated with #deleted.
I can view the table data if I use a passtyhrough query but performing row level and field level edits from there is impossible. Not all people have Studio manager so that is not an option.
We are moving away from our legacy application and the development company has already developed the schema using the BigInt so changing that to a numeric or integer is out of the question. My boss has determined that using a set of bridge tables with identical tables in a different database where we replace the bigint with int will work - we can view the data in the "bridged" database and even update it from select queries and simply by opening the table in view mode from Access.
Here is the hard part (for me) because I have never done this. I can write SQL and understand the security/roles etc, but I have never administered SQL server regarding server side triggers and stored procedures - I have always used the command object or passthrough queries to deal with sql stored procedures. I need to either
A)Create some trigger behind the tables in my bridge table that will fire a stored procedure to replicate the data to the bigint database. They are both on the same sever.
B)Somehow create a "mirror" that always replicates from my bridge database tables (5 of them) to the new database.
Hi,
First of all I want to apologize if I am in the wrong forum. I am new to SQL Server.
I have a table with a field containing email address from user input. Is there a build-in function or script in SQL Server 2005 that I can send a template of a email message to users? If not in what way can I acheive this?
Thanks for all your reply.
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
View 2 Replies View Related