Alter Table Structure
May 9, 2008
have the following table:
RecordKey MonthYear SD1 SD2 SD3
1 Jan 2008 6 AA 0
2 Feb 2008 10 BB 3
3 Mar 2008 12 CC 8
etc....
I need to transform it to this:
1 2 3
Jan 2008 Feb 2008 Mar 2008
6 10 12
AA BB CC
0 3 8
Can this be done in SQL?
View 1 Replies
ADVERTISEMENT
Oct 25, 2015
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 Related
Oct 3, 2006
I am considering the use of ALTER DATABASE ... SET NEW_BROKER within an existing Service Broker instance. The reason is that I have about 23 million old endpoints in Service Broker that I am unable to clean up using the standard END CONVERSATION loops that have been discussed on these forums previously. It's a busy database, and it just cannot do the job within a reasonable timeframe.
There are no critical conversations to be lost, becuse the majority of the old endpoints deal with ASP.NET SQL caching, so they are disposable.
I have read that using the ALTER DATABASE command will delete all past endpoints, but I haven't seen much in the way of the retention of the existing message framework.
If I execute ALTER DATABASE ... SET NEW_BROKER, will it retain everything I have setup, including security, message types, contracts, services, queues, etc.? Is there anything, other than the old conversations and endpoints, that is deleted?
Thanks very much!
View 5 Replies
View Related
Jul 23, 2005
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 Related
Jul 20, 2005
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 Related
Oct 8, 2007
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?
my code:
Alter table customers alter column age
View 7 Replies
View Related
Sep 7, 2007
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;
View 18 Replies
View Related
Dec 16, 2007
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.
View 5 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Nov 20, 2013
I 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
)
View 7 Replies
View Related
Jul 26, 2004
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)
);
View 3 Replies
View Related
Aug 13, 2006
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!
View 2 Replies
View Related
May 26, 2015
which ALTER TABLE/ALTER COLUMN- Statement has a Recreate Table as result ?
View 2 Replies
View Related
Jan 20, 2007
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.
View 6 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Apr 10, 2007
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?
thx
View 5 Replies
View Related
Mar 7, 2008
Structure Number1
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Structure Number2
Category: CategoryId(P.K.), UniqueName, CreatedDate, ModifiedDateCategoryNative: CategoryNativeId(P.K.), CategoryId(F.K.), LanguageId(F.K.), NativeName, Description, Importance, IsVisible, CreatedDate, ModifiedDate
Can anyone tell me that between above 2 structure what is better and why?
I just add CategoryNativeId(P.K.) in Number 2 structure.
View 2 Replies
View Related
Mar 17, 2004
Dear all,
how can I use a store procedure to get the structure of a table like data type, length, description etc.
I have used sp_columns to get the information but it can't get the description of each field.
Thanks.
View 1 Replies
View Related
Jul 13, 2004
How can I get the structure of a table?
With MySql I can do it this way: DESC | DESCRIBE <tablename>
Thank you,
Dirk
Dirk.Ulrich@gmx.de
View 5 Replies
View Related
May 4, 2004
How can I setup a table structure for the diagram shown in the attached bitmap?
1) I need to create a product using labour and materials.
2) I need to create hardware using labour and materials.
3) I need to be able to include some hardware in some products.
4) Some materials in the product are made of a culmination of materials. E.G., Concrete is made of sand, stone, and cement.
Any suggestions?
So far I have :
USE NORTHWIND
Create Table tbProducts (
ProductID int NOT NULL,
Product varchar(50)
)
go
ALTER TABLE tbProducts
ADD CONSTRAINT tbProducts_pk PRIMARY KEY (ProductID)
GO
CREATE Table tbMaterials (
MaterialID int NOT NULL,
Material varchar (50)
)
GO
ALTER TABLE tbMaterials
ADD CONSTRAINT tbMaterials_pk PRIMARY KEY (MaterialID)
GO
CREATE Table tbLabour (
LabourCode char (2) NOT NULL,
Labour varchar(50)
)
GO
ALTER TABLE tbLabour
ADD CONSTRAINT tbLabour_pk PRIMARY KEY (LabourCode)
GO
CREATE Table tbProductMaterials (
fkProductID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterials_pk PRIMARY KEY (fkProductID, fkMaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbProductMaterials
ADD CONSTRAINT tbProductMaterils_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbProductLabour (
fkProductID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_pk PRIMARY KEY (fkProductID, fkLabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbProductLabour
ADD CONSTRAINT tbProductLabour_Product_fk FOREIGN KEY (fkProductID)
REFERENCES tbProducts (ProductID)
GO
CREATE TABLE tbHardware (
HardwareID int NOT NULL,
Hardware varchar(50)
)
go
ALTER TABLE tbHardware
ADD CONSTRAINT tbHardware_pk PRIMARY KEY (HardwareID)
GO
CREATE Table tbHardwareMaterials (
fkHardwareID int NOT NULL,
fkMaterialID int NOT NULL,
Quantity Float NOT NULL
)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_pk PRIMARY KEY (fkHardwareID, fkMaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_fk FOREIGN KEY (fkMaterialID)
REFERENCES tbMaterials (MaterialID)
GO
ALTER TABLE tbHardwareMaterials
ADD CONSTRAINT tbHardwareMaterials_Hardware_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
CREATE TABLE tbHardwareLabour (
fkHardwareID int NOT NULL,
fkLabourCode char(2) NOT NULL,
Manpower int NULL DEFAULT(0),
Hours float NULL DEFAULT(0.0)
)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_pk PRIMARY KEY (fkHardwareID, fkLabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_fk FOREIGN KEY (fkLabourCode)
REFERENCES tbLabour (LabourCode)
GO
ALTER TABLE tbHardwareLabour
ADD CONSTRAINT tbHardwareLabour_Product_fk FOREIGN KEY (fkHardwareID)
REFERENCES tbHardware (HardwareID)
GO
This table structure does not include anything about accounts and item 4) making materials from 1 or more other materials (E.G., Concrete).
Any hints how to incorporate these outstanding items?
Mike B
View 3 Replies
View Related
Sep 11, 2006
How can I get my table structure (columns, their types, whether it s an Identity column) using T-SQL
pls
Thanks you
View 5 Replies
View Related
Mar 10, 2007
Hi
How to write a query for table structure which looks like
SQL Design table.
thanks
asm
View 15 Replies
View Related
Dec 13, 2007
I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)
please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.
I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s)
An example is put all together, (not sure if it is the right and efficient way)
An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data
(table structure) - (example with 3 users)
userId | timestap | message
1 12/12/2007 10:00 some message
1 12/12/2007 11:00 some message
1 12/12/2007 12:00 some message
1 12/12/2007 13:00 some message
1 12/12/2007 14:00 some message
2 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 12:00 some message
3 12/12/2007 13:00 some message
3 12/12/2007 14:00 some message ...etc..
Thank you..
View 11 Replies
View Related
Feb 28, 2008
Hi Guys,
I have another question.
Is there a query to display the structure of a certain table?
e.g. display the columns, data type,...etc.
TIA
View 7 Replies
View Related
Jul 20, 2005
Hi,I am new to XML and need a structure of table in XML format, which needsto be created as a table in another server.The Source and destinationservers are SQL Server 2000. Could anyone help me on this.Balaji.S.--Posted via http://dbforums.com
View 1 Replies
View Related
Feb 11, 2008
Hi,
I have one field "Status" with 15 different types of values. Like 1, 2, 3, 4 .. 15.
Now this Status field contains the values like this:
Id Status
1 1,2
2 3
3 1,2,3
4 13,15
What is the best way to organize this information in SQLServer table?
My idea is Create one table with the following volumns: ID, Status1, Status2, Status3, ... Status14, Status15.
Now the above data look like this:
Id Status1 Status2 Status3 ... Status13 Status14 Status15
1 1 1 0 0 0 0
2 0 0 1 0 0 0
3 1 1 1
4 0 0 0 1 0 1
So, In future If they add new column they can easily add column like status16.
And status table look like this
Status
StatusID Description
1 a
2 b
..
15
Thanks in advance
View 6 Replies
View Related
Jun 24, 2007
All,On my aspx page i have 2 sets of checkboxlists, each with a number of items and i am storing that info in my db. currently i have my table set up this way: InfoID--PK autoUserID-intCheckBoxList1ID-intCheckBoxList2ID-int So a user can select more than 1 item in each checkbox, so this table will contain multiple user-ids and each id from the respective checkboxes. My question is, is there a better way to store this data? I hope this is clear, any input would be greatly appreciated.Thank you.
View 2 Replies
View Related
Aug 30, 2007
Hi....I execute select statement on three tables and i get the following table:
ID
Value
Name
1
10
color
2
20
color
3
30
color
4
40
color
View 5 Replies
View Related
Oct 27, 2007
Hi,I have a table for storing my picture albums here it is:CREATE TABLE [UserAlbum] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [UserID] [int] default(0) , [AlbumName] [nvarchar] (100) NULL , [Audience] [int] default(0) , [Date_added] [smalldatetime] NULL , CONSTRAINT PK_CmmNet_userAlbum_ID PRIMARY KEY CLUSTERED ( ID )) ON [PRIMARY]GO
it has a field called "audience" as the name defines i want to choose who will see my albumsit is integer and as follows:0 value=everyone1=friends only2=private
as i started to write a query for it i came into this problem: for exampleif i write SELECT * FROM UserAlbum where audience=1 then only friends can see the infowhat happens to everyone with value of 0?when i set it to 0, friends should also be able to see the info because it is set for everybody
i noticed i should use "in cluase" like thisSELECT * FROM UserAlbum where audience in ('0','1') > so in this way both everyone and also friends can see the info
or maybe i should add 3 different fields for private,public,friends
it seems to be simple but at the same time harddoes anyone have a suggestion?
View 1 Replies
View Related
Jan 29, 2008
Hi i have a student table and also an societies table, and a student can belong to as many societies as possible, for example student John Taylor belongs to the Football society, rugby societey and dance society, where as student Jim Jenkins belongs to the Football society, rugby society, arts society and graphics society.
My tables so far look like this;
STUDENTS; student_id, student_name
SOCIETIES; society_id, society_name
My question is how can i make the scenario above reflect in my table structure, would i need to create another table, if so what fields or columns should i place in this new table, thank you.
View 7 Replies
View Related
Apr 16, 2008
I'm a VB.net programmer working in an environment that doesn't have a true sql db administrator. We have a situation where we're thinking about adding a new database (db2) with the same Tables used in an existing database (db1). We were hoping to avoid this, but circumstances are forcing us to think about it. Are there any sql tools out there to maintain a dual-db like this? What are the main disadvantages to this?
View 3 Replies
View Related
Oct 27, 2000
I just started with SQL 7 and I would like to know how to print
out the structure of a table in SQL 7.
thanks in advance.
View 1 Replies
View Related
Jul 19, 1998
This has to be a very simple think to do, but, I`m new....
Have NT4.0, SQL6.5, and existing database with about 30-40 tables.
Just want to be able to print out each table structure with record info. Don`t need the actual data printed, just the table/record structure.
Thanks, and again, I`m new to SQL/Query so go easy on me.... ;-)
Ron
View 1 Replies
View Related
