Data copy and overwrite - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Data copy and overwrite

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-14 : 10:50:19

I have a table that gets updated everynight and import it into another table on another database. What is the best way to just overwrite the data every night in the imported database?

Thank you,

Doug

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 11:13:59

truncate destination_table
insert destination_table (col1, col2, ...)
select col1, col2, ...
from source_table

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-14 : 11:44:57

Fred,

I knew that I didn't explain this very well but let me try to do a better job this time.

Database1 is written to nightly at midnight. Database2 is a then appended to by new data from the night before and then a comparison is ran against the data that was in Database2 before the append and the "new" data after the append.

I think what I need to do is simply append the data from Database1 to Database2. Can you give me an example of that.

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 11:54:16

The short answer is: Take my given solution without the truncate-statement...

But you should give
table structure, some sample data and wanted result to be more clear.
Since we don't know your system and we can't see what you can see it is hard to for us to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-09-14 : 12:18:12

My table in Database2 is Memberdata
The structure is like this:

ID (char)
FirstName (all of these are varchar, 50 unless noted)
LastName
Fullname
Speciality
Company
Phonenumber
Fax
Birthday
StreetAddr1
StreetAddr2
City
State
Zip
Last_Updated (datetime)
Date_Added (datetime)

Here is a sample line of data
6001064 Ira Bell Ira Bell, III, MD TEXAS HEALTHCARE (xxx) xxx-7008 (xxx) xxx-xxxx Sep 21 1900 12:00AM 1111 Some Address Pkwy Austin TX 55555 8/18/2010 4:43:29 PM 9/22/1991

Here is the query that i run nightly to see if new data is added:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE YourProcedure AS

insert into msbtotal.dbo.newclients
SELECT tcms_members.dbo.memberdata.* FROM tcms_members.dbo.memberdata left outer join
msbtotal.dbo.memberdata on tcms_members.dbo.memberdata.id =
msbtotal.dbo.memberdata.id where msbtotal.dbo.memberdata.id is
null
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

So as you can see ... it checks to see if there is a null in the ID field and then will put the "new" data in new clients. What's not happening is that when I do a import/export with DTS, is that it should just append data that already exists and insert new data that didn't previously.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources