SQL Update Query To Copy Data
Jul 20, 2005
I'm a bit of a newby to creating update queries so I was hoping those more
experienced could help with what should be a simple query. I do know to
create backups and test on a test database first.
I need to copy an items' cost from location 'CA' to the cost in location
'OH' when the cost in 'CA' is not zero
Table name is ITEMS
Location column is LOC
Cost column is COST
Here's the Select statement from Enterprise Manager's Open Table, Query tool
SELECT loc AS Expr1, cost AS Expr2
FROM ITEMS
WHERE (loc = 'CA') AND (cost <> 0)
I just can't seem to get this into a working Update query.
Thanks in advance...
View 4 Replies
ADVERTISEMENT
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
Jun 4, 2007
I have some code (C#) that runs an SQL update query that sets thevalue of a column to what the user passes. So, this causes an errorwhen anything the user passes in has a ' character in it. I'm surethere's other characters that'll break it too. So, I was wondering,how do I get around this? Is there some commonly accepted regexpattern that will make the value safe to run in an SQL query? How canI take care of any values that need to be escaped?I'm not using any fancy ado.net objects:string sql= [whatever the user passes in]SqlConnection connection = newSqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());connection.Open();SqlCommand command = connection.CreateCommand();command.CommandType = CommandType.Text;command.CommandText = sql;try{int result = command.ExecuteNonQuery();if (result != 1){Response.StatusCode = 500;Response.Write("The file has been uploaded, but wecould not update the DB");Response.End();}}catch (InvalidOperationException){Response.Clear();Response.Write("error");Response.StatusCode = 500;Response.End();}connection.Close();
View 2 Replies
View Related
Apr 7, 2014
SELECT ContactID,FirstName,MiddleName,LastName,Description FROM Contact
Contact table contains 4 columns as explained. in the application there is one tab called Contact where it displays Above information and description is non editable.Now the new requirement has come user can update the description information and save information in a new column say 'Description1'. that means new column needs to be added in the db and also necessary changes needs to be done at the application side
For ex :SELECT ContactID,FirstName,MiddleName,LastName,Description,Description1 FROM Contact
Now when user views the contact table it should display description info by default from 'description' table.If he edits he should see edited data from 'description1' table.the logic should if updated data is there display that data from 'description1' table other wise display from 'description' table
View 9 Replies
View Related
Jul 22, 2014
I have created a table in a database for a football that I want to update as the season progresses. Is there any way I can add the new data to the old data in my columns through SQL rather than searching for the old data, doing the maths in my head and doing a simple update query? I know it doesn't save much extra time but I can get awful lazy when it comes to extra maths!
Example would be:
Player Minutes
Adam 287
Adam has played 287 minutes prior to the most recent match in which he played 67 more. I would like to know if its possible to do an update where I can just simply add 67 onto the existing numbers with one query rather than find Adam's minutes, use a calculator, do a table update.
View 4 Replies
View Related
May 15, 2007
in my original database have a column which is for "path" ,the record in this column is like → �mms://192.12.34.56/2/1/kbe-1a1.wmv】
this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..
I have to change � mms://192.12.34.56/2/1/kbe-1a1.wav】 to � mms://202.11.34.56/2/1/kbe-1a1.wav】
I tried to find the reference book and internet . can't find out the answer for this problem.
can you help? or maybe is it a impossible job?
thanks
View 16 Replies
View Related
Apr 5, 2007
is it possible to make a custom query to fetch and update data from sql server 2005 in SharePoint designer
i make a new data source library and use a custom query to get data but don€™t know how to configure update custom command
can any buddy help me out
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
Nov 1, 2006
Hi. I have done alot of searching around but can't find answer to this specific problem.
Here is my connection string in vb.net app config
<connectionStrings>
<add name="myfile.My.MySettings.ffgscrmConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
The database is locally attached to the vb.net project residing in a data directory in the project. I am testing installation on a virtual pc - with no sql express managment or other sql server databases attached.
When I do a vb.net setup project and run this on, there is no problem. However, once having done that, when I want to update the app, I can't just copy in the new database along with exe. I get the error:"sql server does not allow remote connections (provider:sql network interfaces error 26 - error locating server/instance specified).
Is this a problem of the virtual PC? I am testing this because I need to be able to send updates to another local machine without using setup program.
View 1 Replies
View Related
Feb 23, 2008
I have a series of .csv files created by a parts system. The .csv filename is in the format partnumber.csv The csv file contains a date column and 6 other fields. Each csv file is about 4000 records and there are approx 7000 .csv files that get re-generated once a week.
I'm using c# SqlBulkCopy object to import the csv file into a temp table. No problem there. It works realy fast.
What I need now is a way to move the data from the temp table to the final table and append the partnumber. I'm thinking it would be easy to pass the partnumber in as a paramerter to a t-sql query but not sure how to write the query itself.
I also want to check if the partnuber/datestamp combination from the temp table already exists in the final table and skip it if it does. So I suppose I ultimately need an update query.
Once I have that query written it's easy to import a .csv, launch the update query, wipe the temp table and repeat with the next .csv file.
Temp Table Layout: DateStamp(datetime), Field1 ... Field6(all real's)
Final Table Layout: PartNumber(varchar), DateStamp(datetime), Field1 ... Field6(all real's)
TIA.
View 4 Replies
View Related
Oct 10, 2006
Can I make a copy of my development database DEV on same SQL SERVER machine, rename it to TEST and stored procedures to be updated automatically for statements likeUPDATE [DEV].[dbo].[Company]SET [company_name] = @company_nameto becomeUPDATE [TEST].[dbo].[Company]SET [company_name] = @company_namein order not to edit each individual stored procedure for updating it ?
View 2 Replies
View Related
Dec 10, 2005
Hi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies
View Related
Aug 12, 2006
Can anyone provide an expample of bulk copying XML data to a SQL table. I am also looking at using column mapping so that I can map fields and also insert a new GUID into the key of the SQL table.
Many thanks
View 1 Replies
View Related
Mar 26, 2007
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username
2 FROM users
3 WHERE user_username LIKE '%[!-)]%';
View 2 Replies
View Related
Mar 2, 2008
I have a query that is taking 6 mintues on my db so i copied the 2 relevant tables to a new db for testing and then tested the query and it took only 30 seconds.
why would this be? tables are the same size - i copied to the new by creating the table and then inserting from the old to then new.
these are very big tables.
the first db does have many other tables but why should that make a difference?
View 5 Replies
View Related
Jan 26, 2006
Hi,I want to run a select query and copy the result to a label. I created a SqlDataSource1, but I don't know how to go ahead.I would like to do something like:string x = SqlDataSource1.SelectCommandPlease, advice!
View 2 Replies
View Related
May 19, 2006
Hi All,
I have an issues with running the above. I am trying to import tables from an Oracle Database and want to use this wizard. The problem is that when I use the wizard it is not letting me select the "Copy data from one or more tables or views" it is not selectable, it's greyd out. It will only let me select "Write a query to specify the data to transfer".
Or if someone can provide me with basic a script that will let me import tables that begin with "capa" and "capd". My sql is really awful.
Thanks
Andy
View 9 Replies
View Related
Dec 5, 2005
i have a claims_tbl that i am inserting into with claim information, the tbl contains a claim_seq number that i want to copy over to a user_info_tbl at the same time insert user information below is what i have tried with no luck
insert into user_info(claim_seq) select Max(claim_seq) from claim_tbl
update user_info set lname =upper(@lname), fname =upper(@fname), mname =upper(@mname), personnel = @personnel, p_position =upper(@position)where claim_seq = (Select Max(claim_seq) from user_info)
i am getting duplicate rows of information ..........can someone help
thanks
View 2 Replies
View Related
Jul 25, 2001
Hi!
What is the best way to copy data from SQL server to Access database located on differet machines?
Thank you,
Elena.
View 1 Replies
View Related
Sep 11, 2006
Hello,
there is a database with an table x and I want a copy of that in my testdatabase.
I am pretty sure, that I have done that the whole last week, but it's not working anymore.
I thinkt, that last time I used a statement like that to copy the table with structure and all.
insert into dbtest.dbo.x select * from db.dbo.x
Today it's only telling me, that the destinationtabel x is not present.
The table has about 100 fields and I swear, I didnt create it by hand in the testdatabase, I'm definitely too lazy for that.
I can't use the enterprise manager, I only can use sql.
There are still tables in my testdatabase, I copied last week. So what is wrong with my insert into?
View 1 Replies
View Related
Apr 7, 2008
All,
Does anyone have amethod for copying db(all tables, procs etc..) but without the data. Scripting is not feasible because of number of objects constraints ..etc...
any help would be appreciated
thanks,
JB
View 6 Replies
View Related
May 26, 2008
hi
How to copy data from one column(field) to another column(field).
I have two column colA & ColB
Both column have data.
but i want to replace all ColB data with ColA.
it is like this,
COLA COLB
abcd grfr
dfere kieidk
fere fkerie
but, I want like this
COLA COLB
abcd abcd
dfere dfere
fere fere
please help me
View 2 Replies
View Related
Oct 6, 2005
I am trying to copy item names from one database to another if they share the same code number.
e.g.
UPDATE TABLE1
SET NAME = DB2.TABLE2.NAME
WHERE CODE = DB2.TABLE2.CODE
I dont know how to specify the second db as using a '.' here doesnt seem to work. I have also tried '..' and DB2.dbo.TABLE2 but get the same error - 'The column prefix 'Train_DB_FinancialsProd.dbo.ITEM_DEFINITIONS' does not match with a table name or alias name used in the query.'
I have checked that the spelling is ok so not sure why it doesnt work.
I have also tried using a temp db in between to copy the values across then try to update from the temp table but couldnt make that work either. e.g.
create table #tdhname
(
code char(50),
name char(100)
)
INSERT INTO #tdhname (code,name)
select distinct code,name from Train_DB_FinancialsProd..item_definitions tritm
where code = tritm.code
and tritm.ittyp_refno = 241
update pharmacy_items
set name = #tdhname.name <----- THIS DOESNT WORK
where #tdhname.code = code
Thanks!
View 5 Replies
View Related
Jul 23, 2005
hi their,How can i copy data in MS-SQL 2000 from one table to another withdifferent charactristic?thanks*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Mar 10, 2008
Help!!! I am trying to consolidate 5000 odd databases. Each database contains hundreds of tables. The tables in each database are identical to the tables in all of the other databases. Sounds simple doesn't it? However, SSIS has defied every trick that I have applied to this task including changing connectionstrings at run time through package configuration. has anyone out there ever tackled a similar challenge?. Please let me know
Desperate
View 7 Replies
View Related
Jul 30, 2007
Hello,
I am sure there will be a simple answer to this but it has got me stumped.
Having to move over to Vista with my new machine so I am having to switch to 2005 version for my development but still upload to a 2000 server.
I have had a look at 2005, like the new Management Studio, however I ahve a couple of problems which I can not find the answer to.
Firstly, the SQL Query Builder, where has it gone? I often have to import/export data from Excel files and used to use the SQL query builder to create my queries. If I want to copy all columns it is fine but if I want to import select columns I find it easier to view a list and then just add the ones I want.
Am I missing something here?
Secondly, copying stored procedures, before when running DTS ther were three options, Copy Tables/Views, Data Using Query and Copy Objects.
I used the copy opbjects a lot as it was a very quick way of transfering a group of tables and stored prcoedures that I had created. This appears to have now been replaced with Copy Database, which copies everthing, can can not be used to copy from SQL2005 to SQL2000.
If I want to copy multiple stored procedures from SQL2005 to SQL2000 how is it done now? I have tried finding out but have not been sucessful.
Any help would be greatly appreciated,
Regards,
Lee
View 4 Replies
View Related
Sep 15, 2001
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this:
KeyField
Value1
Value2
Value3
The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
View 1 Replies
View Related
Jul 20, 2005
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Dec 5, 2007
I have SQL Server Express installed on my local development machine and on a remote server. I have identical databases set up on each. How can I copy the data from one to the other? How can I copy the data from one to SQL Server 2005? And can I create an Access database from SQL Server Express? Diane
View 3 Replies
View Related
Dec 5, 2007
I have SQL Server Express installed on my local development machine
and on a remote server. I have identical databases set up on each and I'm trying to keep them co-ordinated to make testing easier.
How can I copy the data from one to the other? How can I copy the data
from one to SQL Server 2005? And can I create an Access database from
SQL Server Express?Diane
View 9 Replies
View Related
Dec 16, 2007
Hi: At the moment, I know how to copy a db to another (create a .bak file), but I am not sure what is the best way to copy one the table (with it data) from a db to another.Would u please give some links or suggestions?Thanks.jt
View 4 Replies
View Related
Dec 31, 2007
I am creating an ASP.Net project at work and have created a database in Microsoft SQL Server 2005. I need to copy data from an old database (which is in Access) that the company has used for years. The problem is that these databases are not very similar (the Access database is not normalized at all). My question is simply this -- is there an easy way to do this. I would like to avoid writing a lot of code if possible as this will only run one time. Any help would be appreciated. Thanks.
View 1 Replies
View Related
Jan 17, 2008
i have a table called t_CustomerAcct in 'Dev' db and want to copy the data in t_CustomerAcct table in 'Production' db.
but i have some records in the table in 'production' db which i dont want to be updated. my primary key in both tables is 'email'
i bit lost on how to do this and i dont want to loose any data from production db accidentally.
View 1 Replies
View Related