Hi, I am kinda new to this so here it goes:
table name: USERS
field names : LName, FName, EmpID
other table name:PERSONNEL
field names : (same as USERS)
The EmpID column in my USERS table is blank (I have 30 records in the table.)
I would like to update the USERS.EmpID table with the PERSONNEL.EmpID data
how would I do this or what would the code be?
sanctos
hi i want to update column with new value in a table is it possible to do so in stored procedure . the name of the column will be an input to stored procedure ie at the time of writing the stored procedure i dont know which column the user will be updating
View 2 Replies View RelatedDoes anyone know of the SQL statement to add additional comuns to an existing table. I know i can do it through enterprise managaer, but I want to see if I can do it from query analyzer or some script to add columns without having to recreate the table. Or a way to save off the data and recreate the table then placing data back in? I hope that makes sense. I am thinking of this from the sens of doing all updates through source safe
Thanks!
My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.
e.g. TABLE2:
e#
--
1
2
3
4
5
5
6
7
7
1
2
3
4
5
UPDATE TABLE1
SET counter = (
SELECT COUNT(TABLE2.e#)
FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#)
GROUP BY TABLE2.e#);
--^Doesn't work
so my TABLE1 should be:
e# counter
-----------
1 .. 2
2 .. 2
3 .. 2
4 .. 2
5 .. 3
6 .. 1
7 .. 2
8 .. 0
9 .. 0
(The .. is just spacing to show the table here)
I'm looking for some performance assistance on updating a column value in a table that contains approximately 50 million rows. I have a permanent table in another database that has the key column and value to be set. My query is listed below, but I'm afraid it will run quite awhile. Any suggestions would be appreciated.
update mytable
set column2 = b.column2
from mytable as a
join mytable1 as b
on a.column1 = b.column1
There is a one to one relationship between the two tables.
Hello,
One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?
Thanks a lot,
Andy
SQL version: 2005, Standard edition.
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
Hi,
I have a table with col_noteText defined as 'Text' datatype column.
I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.
Does anyone know how to do this for 'Text' datatype column.
Thanks in advance.
jfk
Hello, I am pretty new with SQL Server 2005.
I have installed SQL Server Express Edition. I have migrated a set of tables from Oracl10g (by using Microsoft's Migration Tool Kit).While I am trying the following simple update command, the session hangs and it never finishes !!!!!!!!!!!!
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
select pos_key from pos_station where staff_key = 1105
POS_KEY
=======
NULL
update pos_station set pos_key = 1 where staff_key = 1105
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The table has a few constraints and a couple of indices in place.
Then I create another table (but no contraints or indices), just copy the data from the problematic one and the update WORKS (in msecs) :
update pos_station_new set pos_key = 1 where staff_key = 1105
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Is there any way to tell if the table (any table in SQL Server) is corrupted or not ?
How can I tell if a session is waiting for something and what is that something ?
Thank you very much for your help.
Tom
Hi,
I have dataset which has max(column) and between_sale_dates columns So I would like to
update those columns. CustomerNo and Sales_date are important . This group by for these 2
colums. I can write a sproc but How can I do with SSIS?
thanks
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
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;
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
I have my table like this
Code Amount Debit Credit
D 230
C 440
C 890
D 560
I want to update amount in Debit if Code is D
Else i want to update it into Credit
How can i establish this
Please Help
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies View RelatedI receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies View RelatedI have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies View RelatedHi!
I have a "little" problem with nested case model:
-- "normal" database:
DROP TABLE [unitInfo] ;
GO
CREATE TABLE unitInfo (
unitID INT PRIMARY KEY
, beginDate SMALLDATETIME
, area VARCHAR(10)
, partSize INT
, y2predict MONEY
) ;
go
INSERT INTO unitInfo
VALUES (1, '2007-02-01', 'home', 42, 10.0) ;
INSERT INTO unitInfo
VALUES (2, '2007-03-05', 'home', 43, 11.0) ;
INSERT INTO unitInfo
VALUES (3, '2007-02-02', 'office', 11, 11.4) ;
INSERT INTO unitInfo
VALUES (4, '2007-02-01', 'office', 10, 33.6) ;
INSERT INTO unitInfo
VALUES (5, '2007-02-01', 'office', 42, 44.1) ;
CREATE TABLE unitLog (
id INT IDENTITY(1, 1)
PRIMARY KEY
, logtime SMALLDATETIME
, -- combination of logtime/unitID is unique
unitID INT
, -- "FK" on unitInfo
m1 FLOAT
, m2 FLOAT
)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 2, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-01', 3, 63.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 4, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-02', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-03', 1, 423.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-04', 1, 432.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-05', 2, 43.0, 441.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 2, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-06', 3, 43.0, 4.0)
INSERT INTO [unitLog]
VALUES ('2007-01-07', 1, 4.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 3.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-08', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-09', 2, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-10', 3, 143.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 4, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-11', 5, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-12', 2, 43.0, 144.0)
INSERT INTO [unitLog]
VALUES ('2007-01-13', 4, 413.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 4, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-14', 1, 43.0, 44.0)
INSERT INTO [unitLog]
VALUES ('2007-01-20', 1, 43.0, 414.0)
INSERT INTO [unitLog]
VALUES ('2007-01-22', 1, 43.0, 414.0)
-- SSAS:
CREATE MINING STRUCTURE NestedStructure
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure
ADD MINING MODEL nestedModel ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
/* version 1*/
insert into NestedStructure ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
openrowset('sqloledb', Server=myserver;Trusted_Connection=yes;,
'Shape {select * FROM mydb.dbo.unitInfo }
Append ( { select id, unitID, m1, m2 from mydb.dbo.unitLog }
Relate unitID to unitID ) as logdata ')
Parsing the query ...
OLE DB error: OLE DB or ODBC error: Syntax error or access violation; 42000.
Parsing complete
Where is the error?
/*version 2*/
CREATE MINING STRUCTURE NestedStructure1
( unitID LONG KEY, beginDate DATE CONTINUOUS, area TEXT DISCRETE
, partSize LONG CONTINUOUS, y2predict DOUBLE CONTINUOUS
, logdata table ( [id] LONG KEY, unitID LONG CONTINUOUS
, m1 DOUBLE CONTINUOUS, m2 DOUBLE CONTINUOUS
)
)
ALTER MINING STRUCTURE NestedStructure1
ADD MINING MODEL nestedModel1 ( unitID , beginDate REGRESSOR, area , partSize REGRESSOR
,y2predict REGRESSOR PREDICT_ONLY
, logdata ([id] , unitID
, m1, m2
)
) USING Microsoft_Decision_Trees
insert into mining structure NestedStructure1 ( unitID, beginDate, area, partSize, y2predict
, logdata(skip,unitID, m1, m2))
Shape {openquery(dsnDB,'select * FROM mydb.dbo.unitInfo') }
Append ( { openquery(dsnDB,'select id, unitID, m1, m2 from mydb.dbo.unitLog') }
Relate unitID to unitID ) as logdata
Parsing the query ...
Error (Data mining):
INSERT INTO error: The '[logdata].[id]' nested table key column is not bound to an input rowset column.
Parsing complete
Remark that combination logtime/unitID is the natural key in unitLog.
"ID" is the surrugate key.
What is wrong here...?
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
My dataset looks like this:
IDBuyDotComPriceCompanyIDShadowOf
AB CIRCLE PRO199.99203
AB CIRCLE PRO-b2199.99203AB CIRCLE PRO
AB CIRCLE PRO-TB249.99344AB CIRCLE PRO
AB CIRCLE PRO-TB-S10344AB CIRCLE PRO
I need to update the price of an item where the CompanyID is 344 and the ShadowOf is not null. The value in ShadowOf is the same as the ID that I want to get the BuyDotComPrice for. It should be simple, but I keep getting errors.
I use Microsoft SQL 2008
Table structure as follows
Employee
Empno empname salary
commission
I want to have an other employee table named employee_modified
Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
something like
Select empno , empname , salary , commission,
(salary + commission) as derived_colum1 ,
(derived_colum1 + xxxxx) as derived_colum2 ,
(derived_colum2 + xxxxx) as derived_colum3
into employee_modified from employee
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:
Text QueryPlan Plan_handle
Name Value
select id,name,Address from person <showPlznXML... 010101 Table Person
select id,name,Address from person <showPlznXML... 010101 column id
select id,name,Address from person <showPlznXML... 010101 Table Person
[code]....
I am trying to update a users status from Pending to either Approved or Rejected. I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{ protected void ApproveButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Approved"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); } protected void DenyButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn); conn.Open(); cmd.Parameters.AddWithValue("@status", "Rejected"); int i = cmd.ExecuteNonQuery(); conn.Dispose(); }}
View 4 Replies View RelatedHi,Its probably simple but.. How do I update a column by just '1'..for example - heres my code: protected void Button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(); con.ConnectionString = "HiddenConnection"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; Label productIDLabel = (Label)DataList1.FindControl("productIDLabel"); command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID"; command.Parameters.Add("@productID", productIDLabel.Text); command.ExecuteNonQuery(); con.Close(); command.Dispose(); } As you can see in the bold text, I want to add 1 to the numberclickedin column.. and in my primative way Ive just typed +1What should I use instead?Thanks in advance,Jon
View 1 Replies View RelatedHow can I create an update statement that will allow me to fill in a column if the previous column already has data in it? I am trying to do and UPDATE/SET command that allows me to extract information and populate columns within a table. However, the UPDATE/SET has to make sure that it is not overwriting information that is already in the column and if there is, to populate the column next to it, and so on until all the columns are populated.
Here is my table:
Code:
create table #add_diags(
add_diag_1 varchar(10) null,
add_diag_2 varchar(10) null,
add_diag_3 varchar(10) null,
add_diag_4 varchar(10) null,
add_diag_5 varchar(10) null,
add_diag_6 varchar(10) null,
add_diag_7 varchar(10) null,
add_diag_8 varchar(10) null
)
In my UPDATE/SET I am pulling data from another table to populate into the 'add_diag' columns however, I'm not sure how to write in SQL the ability to monitor all the columns, and if for example the first three are full to then populate into the fourth and so on.
Here is my UPDATE/SET statement (while involved, I think I need something in my WHERE clause in order for this to be resolved):
Code:
update ad
set add_diag_1=dsm_code
from #add_diags ad
join Doc_Entity de
on ad.patient_id=de.patient_id
and ad.episode_id=de.episode_id
and doc_code = 'DCDIAG'
join Patient_Assignment pa
on de.patient_assignment_id = pa.patient_assignment_id
and convert(char(8),de.effective_date,112) = convert(char(8),pa.date_discharged,112)
left outer join Doc_Diag_Axis_I_III dx1
on de.doc_session_no=dx1.doc_session_no
and de.current_version_no=dx1.version_no
where dx1.sequence_no=2
and de.is_locked = 'Y'
and dx1.rule_out = 'N'
and is_billable = 'Y'
and dx1.axis_type IN ('1','2')
and de.status in ('CO' , 'SA')
Any ideas? Thanks!
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,