How to insert from multiple rows

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to insert from multiple rows

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2007-11-27 : 09:52:48

Hi,

I need to do an insert into a table based on a SQL read of an Xml document.

The xml document can have multiple nodes which will, of course, translate into multiple rows in my temporary table:


    	 select 
            imageId,
            imageTypeId,
            notes,
            editorId,
            modified
    into #contentImages
    from openxml (@idoc, '/content/document/image', 2)  -- read elements
    with 
    (
            imageId int,
            imageTypeId int,
            notes varchar(1000),
            editorID int,
            modified datetime
    )

The question is, how do I get all the rows of data from #contentImages and insert them into an equivalent number of rows in an actual table? I need to be able to specify the names of the target fields. So, to put it another way, how do I do this:


insert into documentimage (
		documentId,
		imageID,
		version,
		editorId,
		modified)
	select
		@documentId,
		imageId,
		1,
		editorId,
		modified
       from #contentImages

For multiple rows?

Or will that syntax actually do it?

Cheers,
Matt

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 10:13:22

the syntax looks correct. are you getting the wrong results? are you putting the results into the #temp table instead of directly inserting into documentimage because you are performing some processing on the #temp table?

mattt
Posting Yak Master

194 Posts

Posted - 2007-11-27 : 10:17:49

quote:
Originally posted by anonymous1

the syntax looks correct. are you getting the wrong results? are you putting the results into the #temp table instead of directly inserting into documentimage because you are performing some processing on the #temp table?

Hi,

I haven't tested the syntax because it's part of a query handling a big xml document, and drafting a "test" document is difficult. So I'm checking I've got as much right as I can before I try running it.

I'm using a temp table because I thought that's what you had to do when handling Xml data. Is it possible to translate it directly into an actual table?

Cheers,
Matt

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 14:59:45

without the xml to check, this may be what you are looking for...

insert into documentimage
(
documentId,
imageID,
version,
editorId,
modified
)
select
@documentId,
imageId,
1,
editorId,
modified
from openxml (@idoc, '/content/document/image', 2) -- read elements
with
(
imageId int,
imageTypeId int,
notes varchar(1000),
editorID int,
modified datetime
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources