INSERT RECORD FROM DIFFERENT DATABASE

Question

Post reply

INSERT RECORD FROM DIFFERENT DATABASE

allan audi

Right there with Babe

Points: 738
More actions
April 12, 2006 at 12:57 am

#167810

I would like to append records from table1 in database (data1) into table2 in database (data2) under the same sql server. The two table structures are the same. This is a routine process where data in table2 (appending) should be maintained in the process. Can anybody help me with T-sql statement which i can use to set up this process.
Regards,
Allan.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vee Hall of Fame Points: 3028 More actions · Answer 1

INSERT INTO DATABASE1.dbo.TABLE1( COL1,COL2,COL3)

SELECT COL1,COL2,COL3 FROM DATABASE2.dbo.table2

allan audi Right there with Babe Points: 738 More actions · Answer 2

Thanks Nashi, though i was unable to control the appending such that when an insertion as been made then there should be an insertion for the same records but for new ones. any idea. once again i do apprieciate.Allan.

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 3

You can use a trigger or schedule a job to do this periodically.

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

you could use your id/key column:

SET IDENTITY_INSERT DATABASE1.dbo.TABLE1 ON

INSERT INTO DATABASE1.DBO.TABLE1(Col_PK,COL1,COL2,COL3)
SELECT Col_PK, COL1,COL2,COL3 FROM DATABASE2.DBO.TABLE2 WHERE Col_PK NOT IN 
(SELECT Col_PK FROM DATABASE1.dbo.TABLE1)

SET IDENTITY_INSERT DATABASE1.dbo.TABLE1 OFF

**ASCII stupid question, get a stupid ANSI !!!**

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Using WHERE NOT EXISTS instead of NOT IN you can check uniqueness for multiple column key.

_____________
Code for TallyGenerator

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Seems we could use a little more details about what needs to be done..

Is it to just add nonexisting (new) rows from table1 to table2, or is it to update (refresh) existing rows, and then add new, or.... ?

/Kenneth

allan audi Right there with Babe Points: 738 More actions · Answer 7

it is to add the nonexisting i.e. new cases without having to add them again. i have succedded by including an identity field in table 1. Thanks.

Allan.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

So you have added an identity column as primary key in table 1 (the source table)?

Did it not have a natural key before that?

If not, the identity alone won't protect you against duplicates in table 1, you also need a unique constraint on the natural key. Otoh, if you already had a natural key, the identity probably isn't needed.

/Kenneth