Update/Delete/Add Comparing 2 tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update/Delete/Add Comparing 2 tables

Author

Topic

anthonycampbell
Starting Member

4 Posts

Posted - 2008-03-12 : 12:40:17

Hi,

Im pretty new to SQL Server, but was able to create a dsn conenction to our dbf database, and then use sql script to import the table into sql server using this script below:

use cameodata;

/* Drop stock Table */
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'stock'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
)
BEGIN
DROP TABLE stock
END

/* Import stock Table */
select * into stock
from openrowset ('msdasql','dsn=stock',
'select * from stock')

So basically everytime i run this script, it deletes the table in sql server and then imports the latest copy.

The problem is that this table is huge and it takes a while. What Id rather have is a script that i could run that would compare the dsn connected table with the table currently on sql server and if there are any updates then update sql server, if there are items on the sql server that are not the the latest copy, then delete that record on the sql server table and also if there are new items on the lastest copy then add the new record to the sql server table.

Is this possible?

thanks,
Anthony.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-03-12 : 15:15:41

Duplicate post. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98919

Anthony,

Don't cross-post. Pick a single forum and ask your question there. If you choose the wrong one, someone will let you know.

The reason for this is:

1) It makes it easier for you to get an answer. You only have to check one place.
2) It avoids wasting the time of people who answer it in one forum, only to find out it's been answered elsewhere.
3) Cross-posting causes unnecessary clutter for people to read and the server to store.
4) It's just outright rude, and violates all guidelines for every forum or newsgroup back to the NNTP days.

anthonycampbell
Starting Member

4 Posts

Posted - 2008-03-12 : 15:35:47

Apologies for that. Im new to sql server 2005, and to be honest i don't know which category my question belongs to, hence I placed it in 2 different ones. Your point is taken, and I will refrain from making duplicate posts in the future.

regards,
Anthony.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources