Help With MS SQL Server - Updating Data From 2 Tables
Oct 5, 2004
Hi all,
Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables? If yes- what would be the query structure for that? Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it
Thank you in advance.
View 2 Replies
ADVERTISEMENT
May 25, 2008
I have a database which is used for the asp.net login control and i use the same database for my website work too. In this database there are asp.net created tables for login controls and the tables that i have created for the website. Now when i add a user to the website, data is added in the asp.net created tables (like aspnet_membership, aspnet_users). I want to add some of the data that is added to these tables into the tables that i have created. Is there a way i can do this?
View 4 Replies
View Related
Apr 29, 2008
Hello,
I have a ta ble that has 2 columns(licenseplatenumber,vehicletype) and a second table that has 20 or more columns but has(licenseplatenumber,vehicletype) too. The first table got changed once because vehicletype changed from central office for each licenseplatenumber, and i was wondering if there is a way to update the second table with the data from the first table with one or two statements, and not one-by-one. there are alot of data to be changed.
thank you in advance..
View 1 Replies
View Related
Jul 22, 2004
Hi I want to update the grid which in turn will load data to a few tables: the main table and it's lookup tables, I want to write a stored procedure that will check if the data from lookup table already exists and if not it will insert it
but I am only starting to get to know stored procedure structure
could you give me some advice on how to write such procedure I would be veeeery gratefull
View 2 Replies
View Related
Mar 16, 2008
Hi All,
My database's design is set out here. In summary, I'm trying to model a Stock Exchange for a Technical Analysis application written using Visual C++. In order to create the hierachy I'm using a Nested Set Model. I'm now trying to write code to add and delete equities (or, more generically, nodes) to the database using a form presented to the user in my application. I have example SQL code to create the necessary add and delete procedures that calculate the changes to the values in the lft and rgt columns, but these examples focus around a single table, where as my design aggregates rows from multiple tables using UNION ALL:
Code Snippet
CREATE VIEW vw_NSM_DBHierarchy -- Nested Set Model Database Hierarchy
AS
SELECT clmStockExchange, clmLeft, clmRight FROM tblStockExchange_
UNION ALL
SELECT clmMarkets, clmLeft, clmRight FROM tblMarkets_
UNION ALL
SELECT clmSectors, clmLeft, clmRight FROM tblSectors_
UNION ALL
SELECT clmEPIC, clmLeft, clmRight FROM tblEquities_
Essentially, I'm trying to create an updateable view but I receive the error "UNION ALL View is not updatable because a partitioning column was not found". I suspect that my design in wrong or lacks and this problem is highlighting the design flaws so any suggestions would be greatly appreciated.
View 9 Replies
View Related
Dec 13, 2007
I need to copy all the data from all the tables in a database to a copy of this database on another server.
What feature of SSIS should I take advantage of to accomplish this?
We have an SLA for 8am, most times the data warehousing jobs complete at 8:05am. Adding an additional process/set of tasks to this package would obviously make matters so I'm trying to update/copy/replicate the data in the fastest manner. Typically we're talking 2 marts (10-20GB) with 2 large tables (5-10 mill records) and 20 marts (0.5 - 5 GB) with many more smaller tables (~40 tables with record count ranging from 1 to a million)
Additionally please indicate if the design/feature you suggest can handle (pushing schema changes and additions to the target server) schema changes or new tablesviews added to the source database.
My only idea so far...is using the import wizard (in Management Studio) to create an SSIS package (top copy all the tables from one server to another) and saving it to the server, Then executing this package after the job is complete. However this would not work if the schema of a table changed, or if a a table is added. Moreover I don't think I can edit this package in visual studio.
View 3 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Sep 11, 2015
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
View 7 Replies
View Related
Sep 30, 2004
I need to update one row in a SQL Server 6.5 DB from a row in SQL 2000 server DB. What would be the best way to do this?
I have my 2000 server defined as a Remote Server in 6.5, however I get the error message:
contains more than the maximum number of prefixes. The maximum is 2.
View 9 Replies
View Related
Dec 12, 2006
I want to update data on the device which has a sql server mobile database.
the main database is sql server.
I use web services to enable the mobile application to access the server.
Can anyone recommend a way of updating the data on the device without using replication? I basically want to read a dataset from the server via webservice and put this in to local db - but not row by row as too many records.
Regards
View 1 Replies
View Related
Dec 9, 2007
Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
View 1 Replies
View Related
Jan 19, 2008
Consider i have 2 database namely database1 and database2 . both the databases are in the same server
database1 has a following tables
1.the table "class" has following fields and many more fields. consider 50 fields but for the use of example i have given only three fields name and sample values. class studentname rollno (table name - class) 8th std aaaaa 100
2. the table "Fees" has the following fields and many more fields. consider 25 field but for the use of example i have gievn only 2 fields name and sample values rollno fees (table name - fees) 100 50000 101 25000
Now i have created the following tables in database2 the table "class" has the following fields only, class studentname rollnothe table "fees" has the following fields only. rollno fess
Question ?please let me know if there is any tool or method . to transfer values from database1 to databse2
View 5 Replies
View Related
Nov 26, 2005
now i want to update all the three tables with the help of asp.net in the table PhoneExtraFieldAliasalias should be replased by the inputed values say in filed1 = 'date' so now filed1 should behave like as date column and if no data is inputed then field should be 'null' and it picks up its value from phoneextra and phonemst and all the task are performed in one form of asp.net in phoneextra nad phoneextrafieldalias tables has phoneid is comman.first it takes data from phoneextrafiledalias then its value from phoneextra CREATE TABLE [dbo].[PhoneExtra] ([PhoneID] [varchar] (12) NOT NULL ,
[Field1] [varchar] (50) NULL ,[Field2] [varchar] (50) NULL ,[Field3] [varchar] (50) NULL ,[Field4] [varchar] (50) NULL ,[Field5] [varchar] (50) NULL ,[Field6] [varchar] (50) NULL ,[Field7] [varchar] (50) NULL ,[Field8] [varchar] (50) NULL ,[Field9] [varchar] (50) NULL ,[Field10] [varchar] (50) NULL ,[Field11] [varchar] (50) NULL ,[Field12] [varchar] (50) NULL ,[Field13] [varchar] (50) NULL ,[Field14] [varchar] (50) NULL ,[Field15] [varchar] (50) NULL ,[Field16] [varchar] (50) NULL ,[Field17] [varchar] (50) NULL ,[Field18] [varchar] (50) NULL ,[Field19] [varchar] (50) NULL ,[Field20] [varchar] (50) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneExtraFieldAlias] ([CampaignID] [varchar] (20) NOT NULL ,[Field1] [varchar] (50) NULL ,[Field2] [varchar] (50) NULL ,[Field3] [varchar] (50) NULL ,[Field4] [varchar] (50) NULL ,[Field5] [varchar] (50) NULL ,[Field6] [varchar] (50) NULL ,[Field7] [varchar] (50) NULL ,[Field8] [varchar] (50) NULL ,[Field9] [varchar] (50) NULL ,[Field10] [varchar] (50) NULL ,[Field11] [varchar] (50) NULL ,[Field12] [varchar] (50) NULL ,[Field13] [varchar] (50) NULL ,[Field14] [varchar] (50) NULL ,[Field15] [varchar] (50) NULL ,[Field16] [varchar] (50) NULL ,[Field17] [varchar] (50) NULL ,[Field18] [varchar] (50) NULL ,[Field19] [varchar] (50) NULL ,[Field20] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneMst] ([PhoneId] [varchar] (12) NOT NULL ,[PhoneNo] [varchar] (50) NOT NULL ,[Name] [varchar] (50) NULL ,[Sex] [char] (1) NULL ,[Company] [varchar] (100) NULL ,[Address] [varchar] (150) NULL ,[City] [varchar] (50) NULL ,[State] [varchar] (50) NULL ,[Zip] [varchar] (50) NULL ,[Country] [varchar] (50) NULL ,[Email] [varchar] (60) NULL ,[Website] [varchar] (50) NULL ,[Fax] [varchar] (50) NULL ,[EntryOn] [datetime] NULL ,[Remarks] [varchar] (10) NULL ) ON [PRIMARY]GO
View 1 Replies
View Related
Jan 24, 2006
I have a webform contains a list of documents and information about them. What would be the best way to update any changes back to the database where I would need to log each change into an audit table as well.
For example:document changedA YesB NoC Yes
I would need to take this information and update the documents table with the changes from A and C, but ALSO would need TWO entries in my audit log table. One for A and one for C. Is there a quick way to do this or will I need to use loops and/or cursors? I'm trying to find a way that won't kill my performance. Thank you.
View 2 Replies
View Related
Aug 19, 2005
Even if indexes are good, isn't there still a performance issue with updates to a very fat table (333 columns and almost 8030 bytes, with over 100,000 rows). We are looking at 300 users updating this table 400-500 times an hour. I had no input on the table design, but I can complain. All the updates are sql, not sp's
View 2 Replies
View Related
Feb 15, 2008
Hello I have two tables users and Private I want to be able to view, update, delete info from the two tables from one screen
scenario user logs in to system system stores userID and transfers userID to new table this user id will be used by sql to load only that users details (UserID is PK for users and FK for private) the user will be able to update some of their info. as an admin they will use the same procedure except they will be able to see all members details and add new members using update or delete. I have tried to use a data Sqladaptor to do this but the info will display it will not allow delete update. If I load both tables into separate Sqladaptors they are not in sync anyone have any suggestions
thanks
M
View 3 Replies
View Related
Dec 21, 2007
I have two tables:
table 1
-------
code
description
colour
size
active
table 2
-------
code
location
sales_region
active
How can I, using SQL Server 2005, update both tables so that
colour = red
location = Wisconsin
size = medium
active = yes
where code = AL1
please?
table 1 has code as the primary key. table 2 has no index.
View 4 Replies
View Related
Jan 23, 2007
Okay, here is my issue:I have an access program that tracks the location of certain items.When the items are moved their record will be added with transferinformation.So, there are two tables: tblContents and tblTransfertblContents holds all the relevant information about each item as wellas a flag for transferred items and tblTransfer holds all thetransferred item information.My problem is: How do I update tblTransfer when an item in tblContentsgets flagged true?btw, this is using a .asp front end to interact with the database.
View 1 Replies
View Related
Jan 11, 2008
I'm writing a sproc to update a table based on parameters being passed into it. Here is the sproc:
Code Block
CREATE PROCEDURE [dbo].[UpdateLicense]
@VendorId int,
@PoId int,
@LicenseTypeId int,
@LicenseUserId int,
@LocationId int,
@LicenseStartDate smalldatetime,
@DaysAllowed int,
@SerialNum varchar(50),
@ActivationKey varchar(50),
@MaxUsers int,
@Comments varchar(1000),
@LicenseId int
AS
BEGIN
UPDATE license
SET
vendor_id = @VendorId,
po_id = @PoId,
license_type_id = @LicenseTypeId,
lic_user_id = @LicenseUserId,
location_id = @LocationId,
lic_start_date = @LicenseStartDate,
days_allowed = @DaysAllowed,
serial_num = @SerialNum,
activation_key = @ActivationKey,
max_users = @MaxUsers,
comments = @Comments
WHERE license_id = @License_id;
END
When I try to compile this I get the following error:
Error Message
Msg 137, Level 15, State 2, Procedure UpdateLicense, Line 35
Must declare the scalar variable "@License_id".
My first question is what line is line 1? Does the line count include every line, even comments? Becuase, if so, then line 35 is "location_id = @LocationId," which makes no sense to me given the error message about the scalar variable. I am probably missing a comma or something but I cant see it. Anyone?
View 7 Replies
View Related
Mar 2, 2008
Hi,
Is it possible to update two tables with the same stored procedure. I want to update the tables Member an Login using the same SP. If not is it possible to use two stored procedures but the same SQLconnection with two command statements?
e
USE [AdminDB]
ALTER PROCEDURE [dbo].[swim_insert_ipnumber]
@member int,
@mbrFirstName nvarchar(15),
@mbrLastName nvarchar(15),
@mbrEmail nvarchar(15),
@lgnIPnumber int
AS
BEGIN
update Member,, Login
set Member.mbrFirstName = @mbrFirstName,
Member.mbrLastName = @mbrLastName,
Member.mbrEmail = @mbrEmail,
Login.lgnIPnumber = @lgnIPnumber
where mbrMemberNumber = @member
END
View 6 Replies
View Related
Aug 28, 2006
Hello,I read Data Tutorials from this site. here in DAL Different TableAdapters are created for different purpose. I want to know when I want to update More then one table at a time then I have to fire Update Query from more than one Table Adapter. now how to maintain Consistancy? what if one adapter is successed and other fails to update tables in my database ? How to solve this problem???
View 3 Replies
View Related
Dec 16, 2001
I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.
So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.
That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.
Is any body is having the trigger, procedure ready for doing these job. then plz,mail it to me
View 1 Replies
View Related
Mar 19, 2008
Hi,
I'm trying to update a table with a value that is dependent on another table.
Scenario
Table 1 has 2 fields: FieldID, FieldA
Table 2 links each Table 1 row to a specific row in Table 3 using FieldID
Table 3 has 2 fields: FieldID, FieldA
I need to update Table1.FieldA to equal the value in Table3.FieldA in the appropriate row.
I was attempting this along the lines of:
UPDATE Table1
SET Table1.FieldA=
(
SELECT DISTINCT Table3.FieldA FROM Table1, Table2, Table3
WHERE Table1.FieldID=Table2.Table1ID
AND Table2.Table3ID=Table3.FieldID
)
However, I realised that the select query would not know which Table3 row to look at.
I hope that makes sense and if someone can help me urgently I would be most appreciative!
Ian
View 1 Replies
View Related
Feb 8, 2007
hi,
a simple question. I have more that 100 table (132 just). one per month from 1997.
Example:
H0_CONTABILIDAD_199701
H0_CONTABILIDAD_199702
...
H0_CONTABILIDAD_200701
H0_CONTABILIDAD_200702
..
.
.
H0_CONTABILIDAD_200712
Now, how I update all tables with a stantement, sp, etc. Each table have 500k of rows aprox (500.000)
the idea is:
update <table>
set myField = 'newValue'
TIA
View 5 Replies
View Related
May 18, 2007
Is there any gud topic on "updating tables using Views".Plz let me know
View 3 Replies
View Related
Jul 20, 2005
Hi,I want to replicate the system tables of the Northwind database inanother test database that I have created (say NorthwindTest). Istarted with the syscomments table:insert into NorthwindTest.dbo.syscommentsselect * fromNorthwind..syscomments whereNorthwind..syscomments.id not in(select id from NorthwindTest.dbo.syscomments )On doing so,I get the following error:Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not matchtable definition.Does any one know why the two tables are different?Also, if I try to insert on a per row basis, the following worksinsert into syscomments values(53575229,0,1,0,0x280030002900)but the following DOES NOT workinsert into syscomments values(53575229,0,1,0,0x280030002900,NULL,NULL,NULL,NULL,NULL)FYI, the definition of syscomments table is:CREATE TABLE [syscomments] ([id] [int] NOT NULL ,[number] [smallint] NOT NULL ,[colid] [smallint] NOT NULL ,[status] [smallint] NOT NULL ,[ctext] [varbinary] (8000) NOT NULL ,[texttype] AS (convert(smallint,(2 + 4 * ([status] & 1)))) ,[language] AS (convert(smallint,0)) ,[encrypted] AS (convert(bit,([status] & 1))) ,[compressed] AS (convert(bit,([status] & 2))) ,[text] AS (convert(nvarchar(4000),case when ([status] & 2 = 2) then(uncompress([ctext])) else [ctext] end))) ON [PRIMARY]GOThanks in advance,TC
View 2 Replies
View Related
Jul 20, 2005
I have a temporary table that I want to read, update another tablewith the information the delete the record from the Temp table everyhour or so.(The reason for doing this is we have an application that puts data intable1 this has to be moved to table2 before it can be used - thismovement is done using a trigger but can not delete the entry intable1 because the record is locked - so I creat the temporary recordand hopr to clean up later)What is the best way to do this, I was thinking possably an agent ?does anyone have any sample code to get me started.ThanksMartin
View 3 Replies
View Related
May 10, 2007
we have some tables in a many-to-many relationship. when data is changed in a row in one table, simultaneous changes are also made to a second table. this may not be the best way to do what we are doing, but it's the way it is today. if we were to use merge replication with column-level tracking and a conflict occurs in a column in either table, we want the row for both tables to be including in the conflict and resolved together. we don't want the row for a table that no conflict occured to be updated while the other row in the other table where the conflict did occur to not be updated. is there a way to wrap two updates to two tables in a transaction? or link them together in a single "conflict resolution transaction"? i don't see any online documentation referencing this scenario. if there is documentation on this, i would appreciate a pointer to it.
thanks,
bryan
View 1 Replies
View Related
Jun 27, 2007
Although the codes get correctly compiled and executed the tables on the database are not getting updated. I also used the execute sql command manually but it also doesnt work.
:-(
What to do?
View 3 Replies
View Related
Nov 21, 2006
I'm using SQL Server 2005.
Is it possible to update two tables in a single update query by comparing rows in two tables?
Something like this:
UPDATE h
SET
CheckAmount =c.[amount1],
c.Updated = 'YES'
FROM tblCheckNumber as c
INNER JOIN tblHistory as h
ON c.Autonumber = h.AutoNumber
WHERE (h.CheckAmount Is Null Or h.CheckAmount=0)
AND h.CheckNumber Is Null
AND h.CheckDate Is Null
AND h.AccountNumber Is Null;
View 1 Replies
View Related
Nov 24, 2015
I have a two tables stock and sale, after going some sales, should update quantity in stock table
So, I did it as follows.
UPDATE stock
SET quantity -= item.totalQuantity
FROM stock
INNER JOIN (
SELECT sales.barcode
,SUM(sales.quantity) AS totalQuantity
FROM sales
WHERE sales.isQuantityDeduct = 0
GROUP BY sales.barcode
) AS item ON stock.barcode = item.barcode
UPDATE sales
SET isQuantityDeduct = 1
FROM sales
WHERE sales.isQuantityDeduct = 0
This approach has some problem, while updating stock table there may be newly inserted sales. So since I update all record of sale table, stock not updating correctly.
View 4 Replies
View Related
May 29, 2007
I have such a problem:i try to update a row in my table using: protected void selectButton_Click(object sender, EventArgs e) { String taskID = projectsGridView.SelectedRow.Cells[0].Text; usersSqlDataSource.UpdateCommand = "update [Users] set [TaskID]=@task where [UserID]=1"; usersSqlDataSource.UpdateParameters.Add("task", taskID); usersSqlDataSource.Update(); }And i receive error on usersSqlDataSource.Update():You have specified that your update command compares all values on SqlDataSource 'usersSqlDataSource', but the dictionary passed in for oldValues is emptyWhat have i done wrong? Parameter are not set?
View 3 Replies
View Related
Jan 5, 2004
I have two tables in seperate dbs that I need to match up users and update the passwords in the second db table. I know how to match up the data and I'm sure I can do this using a cursor but I was wondering if there is a way I can do this with an update statement. Any suggestions(including telling me I'm stuck using a cursor) would be great.
Thanks ahead of time
EDIT: Nevermind, figured it out :D Thanks anyway.
View 1 Replies
View Related
