Selecting data from one table while storing (inserting) in two?

Question

I want to copy data to a new database. However another table is included in the process where data should be put.

Old table:

DB1.dbo.pages (id, title, content)

New table(s):

DB2.dbo.Page (id)
DB2.dbo.Content (id, pageid, title, content)

How can I select all data from pages while splitting/storing it in Page and Content?

Answer 1

Not possible directly. If your select is expensive, you may consider inserting it into a temporary table and then inserting into the final tables inside a transaction.

Answer 2

what is pageid? is that the id from Page?

If so and this assumes id in the Content table is an identity

insert into DB2.dbo.Page (id)
select id from DB1.dbo.pages

insert into DB2.dbo.Content(pageid, title, content)
select id, title, content from DB1.dbo.pages

Still a little puzzled by this design

Answer 3

Cant u do it with two separate requests? Do it like:

Insert into db2.dbo.page Select id from db1.dbo.pages

And:

Insert into db2.dbo.content (id_page,title,content) Select id,title,content from db1.dbo.pages

I guess this should work.

Answer 4

Yes it is possible to chain two inserts by using the OUTPUT clause:

setup:

create table Page (id int )
create table Content (id int , pageid int, title varchar(100), content varchar(max))

create table pages (id int, title varchar(100), content varchar(max))

insert into pages values (1, 'Lorem', 'Ipsum');
insert into pages values (2, 'eum', 'aliquam vivendo placerat ad');

Actual insert:

insert into Content (id, pageid, title, content)
output inserted.pageid into Page(id)
select id, id, title, content from pages;

Proof:

select * from Page;
select * from Content;

This can be actually usefull because the two inserts are inside a single statement so they are automatically contained in an implicit transaction.