Multiple Inserts statements - 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

General SQL Server Forums

New to SQL Server Programming

Multiple Inserts statements

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-06 : 13:59:40

Hi,
I've 2 databases: regularDB & testDB with exact same schema but different data. Now, I need to transfer some data from testDB to regularDB. I'm trying to write SQLs to perform this but I'm not sure ghow to do it.

I've vendors table & Useremails table as shown below:

Vendors (id, login, password, name)
Useremails (id, vendor_id, email)

id is identity type. I've used below SQL to transfer records to regualrDB.

INSERT INTO regualrDB.dbo.Vendors (login, password, name)
SELECT login, password, name
FROM testDB.dbo.Vendors
WHERE login in ('test123', 'testxyz', 'testpqr');

This has inserted my needed records in regualrDB but the problem is how to insert related records in Useremails table??

Vendor_id in Useremails is referenced to id in Vendors table. Is it possible with SQL statement?

Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-06 : 14:08:20

This maybe?

INSERT INTO regualrDB.dbo.Useremails  (vendor_id, email)
SELECT v.id,u.email
FROM testDB.dbo.Vendors v
INNER JOIN testDB.dbo.Useremails u on v.id = u.vendor_id
WHERE v.login in ('test123', 'testxyz', 'testpqr');

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-06 : 14:12:51

And YET ANOHER Reason to HATE IDENTITY

What if the same ID Exists already in the other database?

Did you set IDENTITY_INSERT ON?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-06 : 15:29:45

Hi vijayisonly,
Thanks for your prommpt response. But I think I did not represent my question correctly. What I need is that I already inserted the records in Vendors table in regularDB from testDB. Now, I want to use this new vendor id which is inserted in regularDB in UserEmails table. How can I achieve it using query?

Using below query, I was able to transfer records successfully from testDB to regularDB for Vendors table.

INSERT INTO regualrDB.dbo.Vendors (login, password, name)
SELECT login, password, name
FROM testDB.dbo.Vendors
WHERE login in ('test123', 'testxyz', 'testpqr');

Now, this query has inserted new id for each vendor in Vendors table in regularDB. The problem is that I need to insert the corresponding records from UserEmails table in regularDB. And while inserting records from testDB, I want to take all the data except vendor_id since vendor_id is already newly created. Basically, I want vendor_id from regularDB & rest of the columns data from testDB. Would you be able to help?

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources