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
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
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:
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?
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.
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.
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.
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!
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.
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.
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
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.
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.
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.
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?
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...
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.
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.
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)
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.
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.
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.
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.
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.
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.
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) );
