Suppose I have a table with the following columns: Year, SalesInEurope,
SalesInAmerica, TotalSales. I want to add a new column called
SalesInAsia, say, but I want it to appear before TotalSales. How can
this be achieved?
Thanks,
Bruno
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
View 1 Replies View RelatedI simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.
The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?
Thanks.
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
View 2 Replies View RelatedI have a query like following
SET NOCOUNT OFF
SET ROWCOUNT 0
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()
[Code] ....
and when i execute it, it gave a return that i expected, but then i want to add a date column on the first column.
How can I test to see if a column exists before adding a column to a sql mobile table?
thanks,
Luis
Hi,I have an application up and running. I need to add a new column to one of the tables which is currently being used - would adding a new column change or cause errors in the current application? e.g. if the table is being accessed by selecting * from table, will adding a new column cause an error? If there is any circumstance where an error would be caused by adding a new column, I will have to create an entirely new table. If I have to do this - how do I get a column in the new table to have the same values as a column in the old table? Can I create a computed column where column=oldcolumn? Thanks,Jon
View 5 Replies View Relatedhi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!
View 2 Replies View RelatedALTER TABLE BFTITLE ADD COLUMN [RATETYPE] [NUMERIC] (10) NOT NULL ;
syntax is not working so can any one tell the right syntax
Sanjeev Dhiman writes "Sir,
How I can add a new column between existing two column...?
Sanjeev Dhiman"
Hi
I have a column with times in
845
930
1015
1145
I need to update the column to look like this:
08:45
09:30
10:15
11:45
thanks in advance
rich
I have a sp: mysp_getstuff it contains the following:SELECT Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?
View 1 Replies View RelatedI posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
View 8 Replies View RelatedI have been working on a website in asp.net1.1 in vb.net2003. I am using a sql2000 server. I am attempting to add a column to my datagrid that will add the total number of wins and output the number in that colum. With some help, I have been able to write the code. However, I am not sure where to put it. Is it a sql function I need to call from my code to add to the win column? Thanks for your help.
View 1 Replies View RelatedI have a table size 2078mb, number of row +530,900. Is it normal for sql to lock users out of the db when I add a column to the end of the table? I'm running SQL 7.0. The table has 4 col regular indexes. No primary keys. It locked the user out for about 10 min. I thought with SQL 7.0 this problem went away?
View 1 Replies View RelatedHello Everybody
We have a SQL Server 6.5 with SP 5a . I want to add an additional column in one of the tables of a database .
Since i don't have much experience on SQL 6.5 , i need your precious help in resolving this case .
Can somebody help me in this regard in a detail step wise manner ? Enterprise manager does not have facility to add new columns to an existing table in SQL 6.5 .
I want to add a Column called 'DM ' with datatype as 'bit' with size as 1 and as non nullable .
Any kind of help will be very precious to me .
Thank you all in advance.
Regards
Rita.
This is a very simple select but I would like to add a final column that adds the QOH, QOB, QOO and Quantity. How can I accomplish this? Thanks
SELECT DISTINCT
zcus_MM_Medsurg_Used.stock_no,
zcus_MM_Medsurg_QOH_Only.qty_on_hand AS QOH,
zcus_MM_Medsurg_Used.QOB,
zcus_MM_Medsurg_Used.QOO,
zcus_MM_Medsurg_Used.QUANTITY
FROM
zcus_MM_Medsurg_QOH_Only INNER JOIN
zcus_MM_Medsurg_Used ON
zcus_MM_Medsurg_QOH_Only.stock_no = zcus_MM_Medsurg_Used.stock_no
Can I add a column to a database table without dropping and recreating the table?
The problem is that everytime a user creates an action that requires a new table - at the moment I drop the table and recreate the table with the new column.
This requires lots of resources as I have to populate the table again.
Is there a design way I can go around this?
I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.
Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?
Any ideas?
Hi,
I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.
Laura
I have a pretty simple SQL query that has two columns that contains only numbers. Is there a way i can add an extra column that simply subtracts the number in one column from number in the other column.I query the data tables rather than have a live link so the .csv file gets overwritten every time i run the query.
View 4 Replies View Relatedhello friends!
I hav a table..which has 4 columns and 5 rows..
whenever i add a new column it goes to end of the table..
Now i want to add a new column in between 2nd and 3rd column..
Is there a way to add a new column to a specified place in a table??
THANKYOU!
I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (aexe.ReturnCode) as '# Patched'
from WrkSta, AeXEvt_AeX_SWD_Execution aexe
where WrkSta.WrkStaId=aexe.WrkStaId
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
and (aexe.returncode='0' or
aexe.returncode ='3010')
and aexe.AdvertisementName like 'MS05-035-043%'
group by substring (WrkSta.[Name], 1,2)
---
Returns :
Location # Patched
EP 102
ES 1986
ET 19
AB 174
SU 6
EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (coll.WrkStaId ) as '# Workstation'
from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId
where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596'
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
group by substring (WrkSta.[Name], 1,2)
---
returns:
Location # Workstations
EP 178
ES 2299
ET 24
AB 215
SU 13
EM 582
What I need is :
Location # Workstations # Patched
EP 178 102
EI 2299 1986
ET 24 19
AB 215 174
SU 13 6
EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else?
Once again I appreciate your help in advance....
i'm trying to add an 'id' column that will auto_increment in a table that i've set up in sql enterprise server. i tried using the code:
ALTER TABLE tablename ADD id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
however, i get the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ID'.
could someone please help (ASAP!!!) with how to fix this.
thanks in advance for any help.
Hi All,
I've one table named tableAB. in that i've added one new column with not null option in the enterprise manager. then i've generated the script, and run the script in client database. because already data is there, it is not accepting to put null value in the new column. so the is missing. anyway backup is there with me.
what is the solution for this....
thanks in advance
I am working on creating a configuration database. This will connect to many databases on many different servers. What I would like to do is run sp_MSForEachDB then store the name of the current server and database the package is connected to. Then add both of these into a column before running sp_helpuser. I don't know how confusing this is, but I think I may have the code worked out pretty well. If you have any questions, please post them and I will try to answer as best as I can. What I would like the ending result to look like is...
<server1> <database1> <userID1> <datareader>
<server1> <database1> <userID1> <datawriter>
<server1> <database1> <userID2> <datawriter>
<server1> <database2> <userID1> <datawriter>
I am at a loss, and I can't use the sys.<table> tables because it has to be backward compatible. Thanks for any help in advance.
Hi All,
I have developed a report using report viewer in asp.net web form. The report is generated based on the selection of Department. User have an option to multi select the departments so the department column grows. Lets say user select Business category first and generate the report then user decides to add another department say Engineering. No the report column headers will have addional column "Engineering". I have used the Matrix since I had to group by Name and dates. Everything is good so far.
Now I have to add an additional column "Notes" at the end of all the columns. If there is ony department is chosen then Notes column appears after that and if mulitple departments are chosen the Notes column should also appear after all the department column (at the very end).
I am really having hard time to accomplish this. Is there any suggestion or solution to finish this? I really appreciate your responses.
Thank you.
Hello,
VS 2005
I am developing the database application. This is a live database and is being used by the customer. I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.There are already over a 1000 rows in this table.
When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:"
The value for column 'DateStarted' in table 'IncidentTask' is DBNull."The method for inserting is:
Code Snippet
Me.dtDateStarted.Value = Me.DsIncidentsControl.IncidentTask(0).DateStarted
What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:
Code Snippet
UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)
Many thanks for any suggestions,
Steve
I am looking for ways to add new column to a table with records inside. If I add the column using this statement:ALTER TABLE Table1 ADD NewColumn1 decimal(18, 2) NULLThe column would appear at the end.
Is there a way to set where the column should be placed? (Excluding dropping all columns and add the columns in sequence again). I know this might not be very important, I am interested in knowing how Enterprise Manager done this, since you can move a column up and down to change their sequence eventhough there're records inside.
I have a csv file of quotes. I need to IMPORT the quotes into my SQL Server database. I created the table with an autonumber identity column, but the IMPORT fails because I can't INSERT NULL into my identity column. There is no option to autonumber or anything when I click the TRANSFORM button on the IMPORT wizard. So, I have decided to allow NULLS on the ID column and add the numbering later. I'd rather not have to manually number it, is there a way to do it with a query? I treid just setting it to not allow NULLS and to auto-incrememnt, but that didn't work....
View 3 Replies View RelatedHi guys
Code:
SELECT C.customerID, quantity, unitprice, (SELECT quantity * unitprice), OD.productID,
FROM customers C
INNER JOIN Orders O
ON C.customerID = O.customerID
INNER JOIN [order details] OD
ON O.orderID = OD.orderID
ORDER BY C.CustomerID
The Output looks a little like this:
Code:
customerID quantity unitprice productID
---------- -------- --------------------- --------------------- -----------
ALFKI 15 45.6000 684.0000 28
ALFKI 21 18.0000 378.0000 39
ANATR 1 28.8000 28.8000 69
ANATR 7 9.2000 64.4000 19
ANATR 10 34.8000 348.0000 72
ANTON 24 16.8000 403.2000 11
ANTON 15 46.0000 690.0000 43
AROUT 25 3.6000 90.0000 24
AROUT 16 19.0000 304.0000 36
BERGS 16 15.5000 248.0000 44
BERGS 15 44.0000 660.0000 59
BLAUS 3 10.0000 30.0000 21
BLAUS 21 34.0000 714.0000 60
BLONP 35 12.5000 437.5000 31
BLONP 15 19.5000 292.5000 57
BOLID 24 17.6000 422.4000 4
BOLID 16 15.6000 249.6000 57
BONAP 40 36.8000 1472.0000 43
BONAP 20 7.7500 155.0000 75
BONAP 8 30.0000 240.0000 7
BONAP 20 6.0000 120.0000 13
BONAP 20 25.0000 500.0000 6
BONAP 20 23.2500 465.0000 14
BONAP 10 9.2000 92.0000 19
BOTTM 16 24.8000 396.8000 10
BOTTM 9 46.0000 414.0000 43
BOTTM 30 4.5000 135.0000 24
BOTTM 21 49.3000 1035.3000 62
BOTTM 15 2.5000 37.5000 33
I would like to add the totals from each customerID and then show the customerID once with the final total amount.
The above SELECT statement was the closest I could come.
The help is always appreciated!
Justin
Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row
basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.
I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.
Thanks!
ALTER TABLE customers
add column active_flg int(1)
but i got error.
quote:Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'column'.
what should i do?
ps. thanks for warning... i won't edit the post after i got the answer anymore.