[Resolved] Update Table W. Data From Another Table
Jan 28, 2008
I have a sp where I have 2 tables. I have already populated ecah table with data.
Now I need to update Table1 with data from Table2.
Key fields between the 2 tables are job_date, job_number and job_phase.
If the record exists in Table1 (reading Table2) need to update record in Table1 with qty_received from Table2.
If record does not exists in Table1, need to insert record into Table1 with job_date, job_number, job_phase and qty_received from Table2
I am very new to sql and wonder if someone would be so kind to help me out? Thank you.
Table1 ie Target
job_date datetime,
job_number char(15),
job_phase char(15),
qty_delivered decimal(8,2)
qty_received decimal (8,2)
Table2 ie Source
job_date datetime,
job_number char(15),
job_phase char(15),
qty_received decimal(8,2)
View 8 Replies
ADVERTISEMENT
Mar 1, 2007
I want to step through each record in one table and extract a School number and concatenate it to a date and insert it in another table. I get an error abour duplicate keys when I run my sp though. each school number is unique. I want to end up with the key of my new table like this
01/03/07*2232
01/03/07*2253 etc
CREATE PROCEDURE spSM_AddWeeks
@dteWeekEnding nvarchar
as
INSERT INTO tblSM_Meals
(ML_Id)
select @dteWeekEnding +"'*'"SchoolNumber from tblSM_Schools
GO
View 8 Replies
View Related
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
Jan 22, 2008
Hello Friends,
I have two tables, And also I have Sample data in them.
create table X
(y int,
m int,
v int)
insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1
select * from X
Create table Y
(fy int,
fm int,
v int)
insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0
select * from X
select * from Y
I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m
Using temporary table cannot be done.
Thanks in Advance,
Babz
View 1 Replies
View Related
Aug 4, 2015
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
View 10 Replies
View Related
Mar 25, 2008
hello all,
I don't know how to update table A with data from table B without specifying every column.
These two tables have the same fields and same structure.
I know that it's possible to do the following:
update table A
set A.name = B.name,
A.job = B.job
from table B
But I have many columns and don't want to describe every column, is that possible?
Thanks!
View 6 Replies
View Related
Sep 21, 2007
Hello,
I'm attempting to explore the functionality of SSIS and all the available data flow transformations, and I'm trying to learn how to use the tool correctly.
How would someone update a table with data which is calculated from that same table.
This is a made up example. Lets say I have a table:
CREATE TABLE [dbo].[tst_source](
[tst_key] [int] IDENTITY(1,1) NOT NULL,
[object] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[color] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[color_count] [int] NULL
)
I would like to populate the field color_count with the count of items listed with that color.
Data:
tst_key object color color_count
1, chair blue NULL
2, table black NULL
3 lamp black NULL
----
So the last column for 1st rec value would be 1, and for 2nd and 3rd, would be two.
tst_key object color color_count
1, chair blue 1
2, table black 2
3 lamp black 2
-- With T-SQL I would use the following code:
update tst_source
set color_count = res.cnt
from tst_source
Inner join (select count(tst_key) cnt,color
from tst_source
group by color) res
ON res.color = tst_source.color
But I would like to use the Data Transformations. Can someone explain in great detail which Data Transformation tasks I would need to use?
Thank you.
View 11 Replies
View Related
Apr 24, 2008
Here is my current Query. (IT's Wrong!)
--------------------------------------------------
UPDATE tblMain
set Measure1 = (select measure1
from tblBulkDump
where tblMain.reportID = tblBulkDump.reportID),
Measure2 = (select measure2
from tblBulkDump
where tblMain.reportID = tblBulkDump.reportID),
DataLocation = (select DataLocation
from tblBulkDump
where tblMain.reportID = tblBulkDump.reportID),
BudgetSource = (select BudgetSource
from tblBulkDump
where tblMain.reportID = tblBulkDump.reportID),
Comments = (select Comments
from tblBulkDump
where tblMain.reportID = tblBulkDump.reportID)
--------------------------------------------------
I need to write an Update for the given fields from the tblBulkDump Tabble.
Thanks in advance,
Gene
View 1 Replies
View Related
Feb 13, 2007
I have a SQL table where most of the fields are numeric. I have tried to run a query in Access to either make a new Access table or insert the records into an empty Access table. However, it changes some of the numeric fields to text fields. Also if I view the design of the SQL table in Access some of the numeric fields appear as text. If I view it in SQL they are numeric. It is a linked table in the Access database.
View 1 Replies
View Related
Apr 23, 2007
hi,
how can I use stored proc to delete all rows from a table (table1) and insert new ones.
for instance.
my query is:
-------------------------------------
select field1, two, three
from table1 innerjoin table2 on etc...
where condition1 and condition2
-------------------------------------
I want to place the result in my table (table1) and everything I run the stored pro I want to delete the content and place the new dataset in the table1?
I hope I'm clear what I'm trying to do.
thank you very much in advance.
P.S please be detailed when explaining.
View 3 Replies
View Related
Dec 26, 2007
Hey all,
Not sure how I did this, but I dropped a table and it still shows up in sysobjects. This breaks a job that rebuilds indexes because the table doesn't exist. I tried recreating and dropping the table, but the extra entry is still there. Would it be okay to just delete the row from sysobjects? This has happened in a 2000 and 2005 db at the same time.
Thanks for any advice.
View 6 Replies
View Related
Jan 19, 2005
I have two tables ingram and ingram_update that have the identical fields. I need to update ingram with the content in ingram_update. How do I do this? Thank you for your help!
View 2 Replies
View Related
May 8, 2008
I have 2 tables...
Table1: FullName, City, State, Data1
Table 2: FullName, City, State, Data1, Data2, Data3
This is what I want to do...
1. For every record that is in BOTH tables I want to UPDATE TABLE 2 with the data from Table 1.
2. Then, if there are "extra"/new records in table 1 I want to append them to Table 2.
How would I do this? Can you provide syntax? I'm new.
Thanks.
View 2 Replies
View Related
Aug 23, 2005
I'm doing a migration and need to clean up a table. I've done this in Oracle but can't get the syntax right for SQL.
Please help. Here is my feeble attempt:
UPDATE tableA
SET tableA.username = tableB.username
FROM tableA
JOIN tableB
ON tableA.email = tableB.email
View 2 Replies
View Related
Mar 11, 2008
helo, i'm the beginner in SQL Server...i have a problem to update table A...i have 2 tables
table A and table B
same field and same structure
i want to update data in table A,but the data i take from table B
table A : id,name,job,address
table B : id,name,job,address
how to insert data from table B to table A
and how to update data in table A?
thx.
View 5 Replies
View Related
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
Sep 28, 2006
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
View 4 Replies
View Related
Dec 15, 2005
I'm trying to update a single record in a table using data from a single record in another table. For the life of me I can't figure this out. I'm trying to avoid having to write a bunch of +=" string stuff in C#. Is there any way to do this with just SQL?Thanks in advance.
View 2 Replies
View Related
Apr 11, 2008
I want to add to a column in one table the data in another. I have tried different update statements and none work. My update statement currently looks like this:
update t1
set t1.amtYTD = t1.amtYTD + t2.Total
from #ProgramData t1 join vw_MER_Reclass t2 on (t1.ProjId = t2.ProjId and t1.task = t2.task and t1.acct = t2.acct)
I need to take the amtYTD in table1, and add to it the value of Total in table 2.
Any help would be appreciated!
View 5 Replies
View Related
Jun 17, 2005
Can anyone tell me how to update a sql server table with the data from excel sheet? That would be very helpful.Thanks.
View 6 Replies
View Related
Sep 20, 2011
I want to update table2.message based on the criteria of table1.name. for example, all records named John will be updated with 'Msg1' in table 2.message. I am using MS SQL 2000 and below is the scenario.
table1 columns
ID
Name
table2 columns
ID
Message
Select a.Id, a.name, b.message
from table1 a, table2 b
where a.id =b.id
a.id a.name b.message
1 John Msg1
2 Steve Msg2
3 Scott Msg3
4 John NULL - update b.message to 'Msg1'
5 Steve NULL - update b.message to 'Msg2'
6 Scott NULL - update b.message to 'Msg3'
7 John NULL - update b.message to 'Msg1'
8 Steve NULL - update b.message to 'Msg2'
If i will update the record per name i am using the query below and i am pre-selecting all the existing names.
update table2 b
set b.message=(Select top 1 b.message
from table1 a, table2 b
where a.id =b.id
[Code] ...
How to update this in bulk without preselecting all the names?
View 7 Replies
View Related
Apr 23, 2008
OK, I'm trying to add some audit type information to a set of tables in my database. This involves adding 2 columns and sticking some data into those two columns. The columns themselves are added successfully, but I'm having troubles sticking any data into them - I'm getting a lot of errors. What I want to do is put a single character code in one column, and a datetime in the second. Here's the SQL I'm trying...
Code Snippet
SELECT @SQL = N'UPDATE ' + QUOTENAME(@TableName) + ' ' +
N' SET TRANSACTION_CODE = ''' + @TxCode +
N''', EFFECTIVE_DATE = ''' + @TxDateTime + ''';'
SELECT @Params = N'@TxCode varchar(1), @TxDateTime datetime'
EXEC sp_executesql @SQL, @Params, @TxCode, @TxDateTime
But this errors out with:
Msg 241, Level 16, State 1, Line 64
Conversion failed when converting datetime from character string.
I tried changing the datetime column to varchar, and CONVERT(varchar(30),@TxDateTime). That gets rid of the above error, but replaces it with the following (twice, I might add - one for each column?):
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Any help is appreciated - let me know if you need more info.
View 14 Replies
View Related
Sep 11, 2015
I need to update the Denominator column in one row with the value from the Numerator column in a different row. For example the last row in the table is
c010A92NULL
I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.
This value is 668 so, the row should look like
c010A92668
create table dbo.TEST
(
MeasureID varchar(10),
GroupID char(1),
Numerator float,
Denominator float
)
[Code] .....
View 7 Replies
View Related
Oct 10, 2007
From: JAGADISH KUMAR GEDELA [jgedela@miraclesoft.com]
Sent: 10/10/2007 4:13:43 PM
To: jgedela@miraclesoft.com [jgedela@miraclesoft.com]
Subject: forum
Hi all,
I need to Insert the XML File data into SQL SERVER 2005 db(table).
For that I created the table with XML Native column (using typed xml)
*********************************create table command************
CREATE TABLE XmlCatalog (
ID INT PRIMARY KEY,
Document XML(CONTENT xyz))
***********************************
In order to Create the table with typed xml ,before that we have to create the xml schema which i
mentioned below
************************************create schema command********
CREATE XML SCHEMA COLLECTION xyz AS
'Place xml schema file ’
************************************
I created the xml schema file by using the xmlspy software.
--------------------------Insert command---------
INSERT into XmlCatalog VALUES
(1,'copy xml file ‘)
-------------------------------
I need to retrieve the xml data from the table
------------select query----------
SELECT Document.query (‘data (/X12//UserId)') AS USERID,
Document.query (‘data (/X12/X12_Q1/header/ISA//ISA_Authorization_Information_Qualifier)')
AS
ISA_Authorization_Information from XmlCatalog.
-----------------
I Need to update/insert/delete the xml data in the table
Can you please suggest the procedure to implement the above requirement(insert/update/delete)
View 5 Replies
View Related
Aug 14, 2015
Is it possible to allow a user to insert and update data in a table but prevent them from performing deletes against that same table? For auditing purposes I need to prevent the end users from being able to delete data.
View 1 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Feb 13, 2014
I have a table dbo.Sales that contains all sales records. There is a column in that table called ItemNumber that I'd like to match with ItemNumber in a flat file and update the ItemCost based on the ItemCost column in the flat file.
So while there will be many sales records for each ItemNumber, I need to loop through and update the ItemCost in that sales record based on the corresponding ItemCost in the flat file. Does this make sense? I really need this for court and I can't figure out how to do it. I took a SQL course about 7 years ago but have forgotten everything.
Database Name: BTData
Database Table: dbo.Sales
Database Columns: ItemNumber (match on this), ItemCost (update this)
FlatFile Name: InventoryCosts.txt
FlatFile Columns: ItemNumber, ItemCost
There will be many sales records for each ItemNumber in the database table. I need to update each one with correct cost based on the item number and cost mapping from flat file.
View 1 Replies
View Related
Sep 17, 2015
I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:
Source Tables :
OC
CREATE TABLE [dbo].[OC](
[OC] [nvarchar](255) NULL,
[DATE DEBUT] [date] NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,
[Code] ....
The update requirement is as follows:
DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
SET rowcount 10000
update r
set Comp=t.Comp
[Code] ....
The update took more than 48h and didn't terminate , how to accelerate it ?
View 6 Replies
View Related
Jul 9, 2006
Every month a client sends a spreadsheet with data which we use to update matching rows in a table in the database. I want to automate this using a DTS package but am having quite a bit of trouble accomplishing what I think should be trivial task. I've been attempting to use a Transform Data Task with a modification lookup but I just keep inserting the rows from the source excel spreadsheet in to the existing destination table without ever modifying the existing data.
Any guidance would be greatly appreciated as to a best practice approach.
View 3 Replies
View Related
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
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
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
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
