MS SQL Server :: Help Finding And Updating Dupes In 2 Tables

Updating Tables In SQL Server 7 Based On Access 97 Tables
Let me start by saying I'm very new to SQL Server... I've upsized my Access Database to SQL Server successfully, but need to keep updating my SQL Server Database with data in my Access 97 database. For example, a table in my Access Database is updated on a regular basis and at certain times, we want to upload that information to our SQL Server database. How can I easily overwrite data in a SQL Server table with data imported from Access 97? I keep getting error messages about the primary/foreign keys...

Any help would be greatly appreciated.

Glenn

View Replies ! View Related

Dupes In My Db
Greetings!

I have a database with several million records, I have found dupes and I need to get rid of them while keeping the original data in the db, kind of like delete all but 1. Any ideas of an easy way to do this?

Thanks,
Jimmy Ipock, MCSE, MCP+I

View Replies ! View Related

Finding Some Tables
I have many tables in one database, and some of them are empty. How can I list / find (for eg. names) only non-empty tables?

View Replies ! View Related

Dupes From A Join :(
I have 3 tables, and im doing a join like:
select top 10 thits.fhits as hits, tmain.fheadline as rubrik, tmain.fpubfile as pub
from thits
join tmain on tmain.postid=tHits.postid
join tkeyscat on tkeyscat.postid=tmain.postid
where tkeyscat.fkeycat=60 order by hits desc

Which works great (almost).

The problem is when an article in tmain is cathegorized in more then
one cathegory, so the join tkeyscat on tkeyscat.postid=tmain.postid
might join in more then one result.

Im trying to select the 10 most read articles from tmain/thits where the article is in keycat 60.

How can I solve this?

tia
/frax

View Replies ! View Related

Urgent - Dupes..
Hi ,

i need to delete duplicate rows in a table, i want a good logic and example to solve this issue. Please help me in this..

urs
vj

View Replies ! View Related

Help Eliminate Dupes
I am VERY new to SQL and I am having a heck of a time biulding a script to find and remove duplicate entries.

Here is the table structure.

CREATE TABLE [dbo].[SecurityEvents](
[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RecordNumber] [int] NULL,
[TimeGenerated] [datetime] NULL,
[TimeWritten] [datetime] NULL,
[EventID] [int] NULL,
[EventType] [int] NULL,
[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EventCategory] [int] NULL,
[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO

This small script seems to eliminate the dupes, but I can't seem to figure out to properly replce the table the with output of the script with all the dupes gone.

select distinct * from dbo.SecurityEventsTest where recordnumber IN
(select recordnumber from dbo.SecurityEvents)
order by recordnumber

Could someone help??

Thank You,

John Fuhrman
http://www.titangs.com

View Replies ! View Related

Finding Updated Tables
I have a front end inteface that adds data to tables in a SQL database.My question is:Is there an easy way of finding out what tables are affected by the update? Is there a sp_ or DBCC command that would give me the update or altered tables?

View Replies ! View Related

Finding Affected Tables
I am attempting to document a sql server 2000 based accounting system.Is there any way to see what tables a stored procedure affectswithout diving into the code?Regards,Ty

View Replies ! View Related

Finding Tables Having A Particular Column
Hi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?

Cheers,
Mathi
India.

View Replies ! View Related

Finding Out All The User Tables Being Used By An Application
Here's the situation, developers inherit a web app from someone,backend SQL db has about 120 user tables and the db is also being usedby other apps. Developers don't have a list of user tables being usedby this app, now, I need to create a new db based on this one, whichwould be used by this app only. So, I intend to find all the usertables being used by this app, then copy its schema and possibly dataas well to a new db. FYI, current ERD is not this app.One option to find out all the user tables being used by this app is todo recursive search for FROM and JOIN against the full spectrum of theuncompiled source code, then, weed through such an extracted list whenin doubt about a particular table, one caveat is a portion of code withsql stmt could have already been commented (no longer being used thoughstill in the code).Better option?TIA.

View Replies ! View Related

Finding The Top 20 Most Used/Active Tables In A Sql 2000 DB
Hi,

I have a legacy sql 2000 database with numerous stored procedure and tables.

I need to find out the top 20 most used/accessed tables in the database

Any one know how i could do this type of trace with profiler?

View Replies ! View Related

Finding If A Record Exists In Either Of Two Tables

Hello,

I have two tables with the same field layout, and they both have the same field as the Primary Key. They just contain different data. I would like to know if a record exists in one, or both, tables.

The tables are InvTemp1 and SalesTemp1. The key for both is stock_number.

Here is the command so far:

SELECT COUNT(*)

FROM InvTemp1 INNER JOIN SalesTemp1 ON InvTemp1.Stock_number = SalesTemp1.Stock_number

WHERE (InvTemp1.Stock_number = '101053')

Thank you for any ideas,
Tom

View Replies ! View Related

SQL Server - Finding The Different Records In Two Identical Tables
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..

View Replies ! View Related

Correct Way Of Finding A Tables Primary Keys??
Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.
I thought that this code did the trick.
***
DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='contact_pmc_contact_relations'
Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@c, 1, Datalength(@c) - 1)
***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.
Here is a dump from one of the tabels where it doesn't work.
SELECT     *
FROM         sysindexkeys
WHERE     (id = 933578364) <--id of the table
***
id                indid colid keyno
933578364   1       1       1
933578364   1       2       2
933578364   2       1       1
933578364   3       2       1
933578364   4       3       1
933578364   5       4       1
933578364   6       5       1
933578364   7       6       1
933578364   8       7       1

Not sure if that dump made any sense, but i hope it did.
If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?

Regards
/Anders

View Replies ! View Related

Deleting Dupes In Special Cases
I need to delete all rows that match at least one of the account_id values of another row *and* that has the same email address. However, if they have the same email address and none of the account_id values then I need to keep it. I've attached a sample dataset along with the expected results.

I have this:
DELETE [acctID_emailAddress_tmp] FROM [acctID_emailAddress_tmp]
JOIN
(select emailaddress, account_id, max(contact_id_tmp) max_cid
from [acctID_emailAddress_tmp]
group by emailaddress, account_id) AS tempImportTable
ON tempImportTable.[emailaddress] = [acctID_emailAddress_tmp].[emailaddress]
WHERE [acctID_emailAddress_tmp].[contact_id_tmp] < tempImportTable.[max_cid]
AND tempImportTable.[account_id] = [acctID_emailAddress_tmp].[account_id];

but it doesn't work since it's keeping the subset of the dupe row(s).

Can someone shed some light?

TIA

View Replies ! View Related

Finding Mismatched Rows Between Identical Tables Based On 2 Or More Cols
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)

View Replies ! View Related

Updating Tables
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 Replies ! View Related

Updating The Tables
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 Replies ! View Related

Updating Changes To Two Tables Using T-SQL
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 Replies ! View Related

Updating Two Tables
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 Replies ! View Related

Updating Two Tables
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 Replies ! View Related

Updating Very Fat Tables
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 Replies ! View Related

Updating 2 Tables

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 Replies ! View Related

Updating Tables
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 Replies ! View Related

Updating Two Tables
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 Replies ! View Related

Updating Data In Two Tables
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 Replies ! View Related

Updating Multiple Tables
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 Replies ! View Related

Updating System Tables
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 Replies ! View Related

Updating Tables Via Agent ?
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 Replies ! View Related

Updating Multiples Tables
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 Replies ! View Related

Updating Of The Tables In Two Different Databases
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 Replies ! View Related

Updating Multiple Tables
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 Replies ! View Related

Updating Data From 2 Tables

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 Replies ! View Related

Updating 2 Tables In One Query
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 Replies ! View Related

Updating Multiple Tables

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 Replies ! View Related