I Want To Update My Table But It Has 30 Fields So How Can I Modify My Table I Have To

May 29, 2008

I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it


I need that query for alter table

in it


reply me soon

View 14 Replies


ADVERTISEMENT

Update Certain Fields In User Group Table With New Info

Apr 28, 2014

I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.

Here is the SELECT statement
SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu,
user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id,
user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex,
user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,

[code]....

We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is

UPDATE user_group
SET user_group.description = Consultants.description
FROM user_group
INNER JOIN Consultants
ON user_group.description = consultants.description
WHERE name like 'Adam Froth%'

but it keeps erroring and saying that it could 'Not be bound'.

View 2 Replies View Related

Update If Exist-one Table To Another From Tow Date Fields- Single Row For Each Day

Apr 20, 2008

question need help
how can i use this code below not for insert
i need it for update another table but only if exist
the link to the code in this FORUM
http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True






Adam Haines wrote:









GPS,



Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.



Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.

Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.


Now the code to get the results you need:



Code Snippet
declare @t table(
id int,
fname char(4),
Start_Date datetime,
End_Date datetime,
val_holiday int
)
insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 )
insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3)
insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4)
insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )

select id, fname, cal.ISODate, val_holiday
from @t t1

inner join Calendar cal
on cal.isodate >= t1.start_date and
cal.ISODate <= t1.end_date







is possible to do it
TNX

View 4 Replies View Related

SQL Server 2008 :: Capture Sessions Which Modify A Table With Details Modified In Table?

Apr 10, 2015

I created am inventory table with few columns say, Servername, version, patching details, etc

I want a tracking of the table.

Let's say people are asked to modify the base table and I want a complete capture of the details modified and the session of the user ( ) who (system_user) is actually modifying the details.

View 1 Replies View Related

How To Modify Colum (alter Table) Of Table That Is Replicated?

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

View Field Content Update When Real Table Fields Change

Sep 1, 2005

Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas

View 4 Replies View Related

Finding Rows In One Table But Not In Another Table Based On 2 Fields

Apr 23, 2008



I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.

View 7 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

How To Modify Table Structure

May 31, 2002

Hello
I have replicated 1 database.Now i need to change the datatype length of one feild from varchar 12 to varchar 20.But when i try to do that it shows error ODBC error !Cannot modify database because it is in replication mode.
Can any body help me to sort out this problem.
Thx
Bilal

View 1 Replies View Related

Using Modify Table Command

Nov 22, 2007

Hi AllAm new to sql server to sobear with me, have checked around but cantfind an answer to this.I want to change fieldname from nvarchar(50) to nvarchar(255) as partof a scriptAm running this command :ALTER TABLE MYTABLE MODIFY fieldname nvarchar(255) nulli test this out in the sql window but cant get it to run error is:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'MODIFY'.Can anyone help ?

View 1 Replies View Related

Trigger To Modify A Table

Mar 19, 2008

I have a 3rd party program that creates some tables in my sql server2005 database, then it does inserts on the tables. Is there a way Icould create a system level trigger that would immediately andautomatically modfiy the table structure after the table is created(hopefully before the inserts occur)? I need to change a field fromnumeric(5, 0) to numeric(15, 0).Thanks,Roger

View 3 Replies View Related

ALTER TABLE MODIFY

Jan 19, 2006

hi!

i encountered problems when running this code in SQL Query

ALTER TABLE [dbo].[amsSchedule]
MODIFY(CutOff1 datetime NULL,
[FileName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

my aim is to modify the two fields to change its data type. BUt when im trying to run this command in the query analyzer, itsays "incorrect syntax error '(' "

What do i have to do? please help me...thanks

View 3 Replies View Related

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani

View 3 Replies View Related

Creating Table Fields Dynamically From Another Table Row

Aug 31, 2006

Hello all:

Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )

Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int....)

The table should look like as:

Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==

I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

A small rough snippet of the code will be appreciated....

Waiting for replies........
saby

View 1 Replies View Related

HT Insert Table A Record Fields Into Table B

Jun 13, 2008

All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.

How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)

Thanks!

-Kurt

View 1 Replies View Related

Modify Table To Turn OFF Autoincrement ID

Oct 4, 2013

I've a table with an ID column, it is an auto increment id column that increases by 1.

ID
--
9724
9725

When my webhost restarts SQL, or some other random act of God, this ID jumps by 1000. Today it leapt from 9725 to 10725, which just doesn't work for me. It results in my having to upload the last known good copy of the database.

My host says they can't change the 1000 row buffer, MS apparently doesn't deem this an issue, and I'm quite frustrated. I used to be able to modify certain settings in SQL since I was using an SQL 2008 database under SQL Management Console but since my host upgraded to 2012, and I'm running XP, there is no forward compatibility and many options are no longer possible (eg. modifying a column, now it has to be done via clicking query and entering a SQL command rather than just right clicking on the column)

So long story made short... is there a way to modify my table (named "places") to turn OFF the autoincrement Id and perform a simple means to figure out the next possible value?

example:
id, place, locstion, gps, v2, v3, v4, comment
================================================
9720, Bill's House, Toronto, 43.4453, y, n, c, This was a good place to eat
9721, Dime a Dozen, Cambridge, 42.4453, n, e, d, A good place to take photos

I'd have to pull the highest value of ID (+1 to it) and then do a standard INSERT into places (id, place, location, gps, v2, v3, v4, comment) values (9722, etc...)I guess my concern is if the autoidentity is turned off, will SQL allow a new value to be entered via INSERT? I'm also using the @@identity to find the last known ID when I want to direct the user to the entry that they've just created and I don't know what that # is just yet. Would the @@identity still work?

View 5 Replies View Related

ALTER / MODIFY / OverWrite Table?

Apr 10, 2008

Hi all I was wondering how to do an ALTER command on a Table but without specifying Column Names but rather attempting to overwrite the Table itself with the new fields specified? For instance if I have Table_1 consisting of the following fields:

IDFirstNameSurname

Then use the following ALTER command:




Code Snippet
ALTER Table Table_1
(
ID Int,
FirstName VarChar(50)
)
This would then drop Surname from the Table and leave only ID and FirstName inside it. Is this possible? I have been searching google but can't seem to find what I am looking for.

Thank for comments + suggestions. Regards, Onam.

View 2 Replies View Related

How To Modify Column In A Table With Replication?

Mar 2, 2007

Dear all

I have a problem about replication, so I want to modify some columns in a table, but my database using replicate. How to do it?

Please solution.

Thank.

PC Kaveesin.

View 4 Replies View Related

Modify Table Structure Via Stored Procedure(?)

Jun 9, 2008

Our ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.

They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.

This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.

Is it possible to do stuff like that in a SP?

View 1 Replies View Related

Read, Modify Table (locking) Question

Feb 3, 2006

I am quite new to MS SQL, and I want to read rows from a todo table, andwhen a row has been processed, I want to delete that row.Ages ago in MySQL I would probably have locked the table, select, processa row, delete a row, unlock the table.I have been reading through the documentation from MS SQL, but it's notclear what exactly I should do.Since I want to lock only one table in the select (the others just providedata, and are not modified), what's a good solution?--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html

View 2 Replies View Related

Open Table With Query To Modify Data

Jul 23, 2007

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.



In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?



I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.



Please help, it seemed so much easier in 2000!

View 7 Replies View Related

Can A View Be Used To Modify More Than One Underlying Table With A Single Statement In MS SQL?

Mar 20, 2008

What's the answer for this and how ? If it is yes then How ?

View 4 Replies View Related

How Do I Copy A Fields From Another Table Into A New Table?

Oct 23, 2007



I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.


Table Fields

Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id


The new table is called Tbl_Sales_Growth
Here's the information on the new table:






Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]




View 2 Replies View Related

Does Anyone Know How To Modify Table Structure In Sql Server Management Console(2005)? Thanks

Jun 16, 2005

does anyone know how to modify table structure in sql server management console(2005)? thanks

View 1 Replies View Related

Transactional Repln,how To Modify The Width Of A Column Of A Table Which Is Replicated

Aug 3, 2006

Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.

I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.

This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)

MH (source) -> MH1 (Replicate)

The column €œcol1€? had width of varchar(10) and this was altered to varchar(100).


insert into MH..test_mh values(4,'abcdeabcdefff')

select * from MH1..test_mh

exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go

exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go

alter table test_mh alter column col2 varchar(100) null OR

MH1..sp_help test_mh

exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go

exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go


Needless to say, help would be apreciated -
~Mihir

View 4 Replies View Related

SQL 2012 :: Script To Find Current Date Modify Table List?

Jun 10, 2015

Need a script to capture current date modify table list(12 AM to 11:59 PM PST) in a database.

View 7 Replies View Related

I Imported A SQL Table Into SQL DataBase, But I Can Not Update This Table Even With SQL Server Management Studio

Jan 8, 2008

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
When I change any data on mentioned table above, Red exclamation sign appears left of the record .
How can I correct this problem?
 Thanks.

View 1 Replies View Related

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

CREATE TABLE #Temp
 (
  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,

[code]....

Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Trying To Save Editted Textbox Value In Table But Original Value Saves Instead - Trouble With Table Update Query

Jan 9, 2008

This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.

View 1 Replies View Related

Using LIKE To Find Any Of Fields In One Table In Fields Of Another

Jul 31, 2013

I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).

Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.

View 5 Replies View Related

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

Jan 31, 2006

I am working with the following two tables:

Category(NewID,OldID)
Link(CategoryID,BusinessID)

All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..

UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID

Please help me with the code.

Thanks,
anisysnet

View 1 Replies View Related

How To Alter The Table With Delete/update Cascade Without Recreating The Table

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

SQL Server 2012 :: Update Table From Variable Table Column?

Oct 6, 2014

I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.

This is my statement:

UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved