November 7, 2005 at 3:59 am
Hello,
I'm trying to export data from one linked SQL Server and insert into tables in the other SQl Server. I can do a Select to get the data from the tables in the first database, but in the new database I need to Insert it into a Company table and a Centerlink table. The Centerlink table is linked to the Company table on the Centerlink.Companykey field. The Company.Companykey field is the primary key and is autocreated.
So my Select from the old database gets all of the fields I need, but in the new database I need to do the Insert into 2 separate tables and use the Company.Companykey field once the Company record has been created.
What's the best way to do this, should I use 2 separate Selects from the old database, and then 2 separate Inserts into the new database. Somehow I would then need to have a Select statement within the Insert of the Centerlink table so that I could query the Company table to get the Companykey value. Or is it possible to do it in one step?
Thanks,
Brad
November 7, 2005 at 6:55 am
It is not possible to insert into two tables with one insert statement.
Regards,
gova
November 7, 2005 at 7:20 am
You will have to do two INSERT/SELECT queries
If company has a guaranteed unique column value (eg Comapny Name) then you can join the company table on the 2nd query to get the id
If not then I suggest you create a temp table (including it's own idenntity column), insert the data from the linked server to this table, insert into the company table, use SCOPE_IDENTITY() to get id of the last record inserted.
Insert into the 2nd table and use the SCOPE_IDENTITY() value above (minus the number of records) plus the indentity column in the temp table to get the id of the company record.
This is probably the best way unless the source table is static otherwise the 2nd insert would have more records than the first.
Far away is close at hand in the images of elsewhere.
Anon.
November 7, 2005 at 2:56 pm
Create a view on your 2 tables to repeat schema of the table you are going to import data from.
Create trigger "INSTEAD OF INSERT" on this view.
Within a trigger you automatically have temp table named "inserted", you can use SET IDENTITY_INSERT ON|OFF, create temp tables with identity and use many other tricks.
At the end you just insert data into this view.
_____________
Code for TallyGenerator
November 7, 2005 at 3:52 pm
CREATE VIEW dbo.TransView
AS
SELECT T1.Col2 as VC1, T1.Col3 as VC2, T2.Col1 as VC3, T2.Col2 as VC4
FROM Table1 T1
INNER JOIN Table2 T2 on T1.Col3 = T2.Col1
GO
-- Say Col1 in Table1 is Identity
CREATE TRIGGER dbo.TransView_Insert
ON dbo.TransView
INSTEAD OF INSERT
AS
BEGIN
CREATE TABE #inserted (
New_Id int IDENTITY(1,1) NOT NULL,
Col1 int null,
Col2 varchar null,
....
Insert #inserted (Col1, Col2, ...)
Select VC1, VC2, ...
FROM inserted
DECLARE @MaxId int
SELECT @MaxId = MAX(Col1) from Table1
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1 (Col1, Col2...)
SELECT @MaxId + NewId, Col1, ...
FROM #inserted
SET IDENTITY_INSERT Table1 OFF
END
It's just schema, arrange it according your real tables.
_____________
Code for TallyGenerator
November 8, 2005 at 3:38 am
Thanks for your help. I've done it with two INSERT/SELECT queries. I was able to join the Company table in the second query to allow to get me the CompanyKey value.
The time of day that the Inserts run will mean that the tables will be static so rthere shouldn't be any data integrity issues.
Regards,BradViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply