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

Update Query Containg Static Data And Data From Another Table.

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

Escaping Data For Update Query

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

T-SQL (SS2K8) :: Query Regarding Update Data Using Application

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

Sum Old Data And New Data In Update Query

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

Hard Question.. Is It Possible To Update Many Column Of Data In 1 Query?

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

Using Custom Query To Retrieve And Update Data From Sql Server In SharePoint

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

Transact SQL :: Query To Update A Table With More Than 150 Million Rows Of Data?

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

Cannot Copy Sqlexpress To Another Pc For Vb.net 2.0 Project Update

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

Table Copy/Append/Update Question

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

Make A Copy Of SQL DB And Update/change Stored Procedures

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

How To Copy Data From Backup Or Data Files To New SQL Installation?

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

Bulk Copy XML Data To SQL Data Source

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

Update Query To Update Separate Chars

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

Query Is Slow On My Db But Not Copy

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

How To Copy Query Result To A Label?

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

SQL Server Import Copy Or Query

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

Copy Data From 1 Tbl To Another Tbl

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

Copy Data

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

Copy Data From One Db To An Other Db

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

Copy Db W/o Data

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

How To Copy Data From One....

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

Cant Copy Data From One DB To Another.

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

How Can I Copy Data

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

Data Copy

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

New SQL 2005 - Where Has Query Builder And Copy Objects Gone?

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

UPDATE Query To Update One Table From Another

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

Update Trigger - Update Query

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

How Can I Copy SQL Express Data?

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

How Can I Copy Data To/from SQL Express?

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

Copy A Table With Its Data From A Db To Another

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

Need To Copy Data From Access To SQL

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

Copy Data From One Table To Another From 2 Db's.

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







Copyrights 2005-15 www.BigResource.com, All rights reserved