How to insert results of select

Question

I am a C# dev learning SQL.

I need to publish data from one database, to another which already has the same structure and which are both on the same instance of Sql Server. By publish, I mean I want all the id's and data to be exactly the same.

The second database is an exact copy of the first.

This is the query:

Select vev.* from [dbo].[metadata_document_set] mds

inner join
[dbo].[document_metadata] dm
on mds.metadata_document_set_id = dm.metadata_document_set_id

inner join [dbo].[segment_metadata] sm
on dm.document_metadata_id = sm.document_metadata_id

inner join [dbo].[element_metadata] em
on sm.segment_metadata_id = em.segment_metadata_id

inner join [dbo].[valid_element_value] vev
on em.element_metadata_id = vev.element_metadata_id

where mds.code = 'PA'

I need to insert all the rows of valid_element_values, as well as element_metadata, segment_metadata, and document_metadata.

How do I do that?

Then you can simply run a `INSERT` statement. Remove `vev.*` and mention the columns from all four table in same order as your Insert column list — Pரதீப், Jan 20 '17 at 15:06
@Prdp, I don't understand what you mean by 'mention the columns from all four tables.' An example would be great. — Greg Gum, Jan 20 '17 at 15:09
do you want to insert these rows into one table? or into separate tables? — Stephen, Jan 20 '17 at 15:23
This may have been answered here. http://stackoverflow.com/questions/2043726/best-way-to-copy-a-database-sql-server-2008 — old_guy, Jan 20 '17 at 15:26
then you will have to insert one table at a time, cannot do it in one join — Stephen, Jan 20 '17 at 15:33
If any table use`IDENTITY` columns be careful, they would need special treatment — Horaciux, Jan 20 '17 at 15:38

Stephen · Answer 1 · 2017-01-20T15:45:51.600

2

If you are inserting to separate tables, you will have to do it one insert at a time.

insert [new_database_name].[dbo].[metadata_document_set]
Select * from [old_database_name].[dbo].[metadata_document_set] 
where code = 'PA'

insert [new_database_name].[dbo].[document_metadata]
Select * from [old_database_name].[dbo].[document_metadata] 
<filter as required>

and so on.

This assumes your tables are exactly the same.

NB This will not work if you have INDENTITY columns.

edited Jan 20 '17 at 15:45

answered Jan 20 '17 at 15:35

Stephen

1,532
1
9
17

@Horaciux *disclamer* added – Stephen Jan 20 '17 at 15:47

Pரதீப் · Answer 2 · 2017-01-20T15:52:19.623

Select vev.* will select columns only from valid_element_value table so remove vev.* from select and mention the columns from all four table in same order as your Insert column list

Considering the target tables are created with all the required column in target database

Update : Looks like you want to insert into four different tables. Then you need four different Insert statements

INSERT INTO target_database.SCHEMA.target_valid_element_value
            (vev_col1,
             vev_col2,
             ..)
SELECT vev.col1,
       vev.col2,
       ..
FROM   [dbo].[metadata_document_set] mds
       INNER JOIN [dbo].[document_metadata] dm
               ON mds.metadata_document_set_id = dm.metadata_document_set_id
       INNER JOIN [dbo].[segment_metadata] sm
               ON dm.document_metadata_id = sm.document_metadata_id
       INNER JOIN [dbo].[element_metadata] em
               ON sm.segment_metadata_id = em.segment_metadata_id
       INNER JOIN [dbo].[valid_element_value] vev
               ON em.element_metadata_id = vev.element_metadata_id
WHERE  mds.code = 'PA' 


INSERT INTO target_database.SCHEMA.target_element_metadata
            (em_col1,
             em_col2,
             ..)
SELECT em.col1,
       em.col2,
       ..
FROM   [dbo].[metadata_document_set] mds
       INNER JOIN [dbo].[document_metadata] dm
               ON mds.metadata_document_set_id = dm.metadata_document_set_id
       INNER JOIN [dbo].[segment_metadata] sm
               ON dm.document_metadata_id = sm.document_metadata_id
       INNER JOIN [dbo].[element_metadata] em
               ON sm.segment_metadata_id = em.segment_metadata_id
       INNER JOIN [dbo].[valid_element_value] vev
               ON em.element_metadata_id = vev.element_metadata_id
WHERE  mds.code = 'PA'

Regarding Identity, If you have identity column in your target tables and you want insert values from source for those columns as well then

set Identity_insert target_table ON 
<<Insert statement>>
set Identity_insert target_table OFF

If you dont want to explicitly insert values into identity column then dont add identity column in Insert & Select column list

You are not considering the use of `Identity` columns – Horaciux Jan 20 '17 at 15:43 — Horaciux, Jan 20 '17 at 15:43
@Horaciux - Updated my post for `Identity` property – Pரதீப் Jan 20 '17 at 15:52 — Pரதீப், Jan 20 '17 at 15:52

score 0 · Answer 3 · answered Jan 20 '17 at 15:10

Lets say you have [dbo1].[metadata_document_set1] table (and it has all the columns in select query)then

INSERT INTO [dbo1].[metadata_document_set1]
Select vev.*,em.*,sm.*,dm.*,mds.* from [dbo].[metadata_document_set] mds        
inner join [dbo].[document_metadata] dm

on mds.metadata_document_set_id = dm.metadata_document_set_id

inner join [dbo].[segment_metadata] sm
on dm.document_metadata_id = sm.document_metadata_id

inner join [dbo].[element_metadata] em
on sm.segment_metadata_id = em.segment_metadata_id

inner join [dbo].[valid_element_value] vev
on em.element_metadata_id = vev.element_metadata_id

where mds.code = 'PA'

How to insert results of select

3 Answers3