Mixing select and variables in insert statement

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)

Mixing select and variables in insert statement

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2007-12-11 : 10:24:05

Hi,

I've built the following SQL which doesn't seem to error, but which doesn't actually work. I've tested to make sure there's content in my temp table, which there is:


insert into documentimagedraft (
    documentId,
    imageID,
    version,
    editorId,
    modified
)
select 
    @documentId, --this is set elsewhere
    imageID, --this is coming out of the temp table
    1, --this is a contstant
    editorId, -- this is coming out of the temp table
    getdate()
from #contentImages

I assume this is going wrong because I'm trying to mix variables, constants and other fixed values with column values coming out of the temp table.

Is there any way I can do this? I'm in a bit of a bind because I need to be able to insert into DocumentImageDraft a number of rows equal to that in the temp table, with some of the values from that table. However the temp table itself comes from XML using a similar insert/select syntax, so I can't actually insert the other required values into the temp table as it's generated (and the values aren't in the XML either):


select 
    imageID,
    imageTypeID,
    notes,
    editorId
into #contentImages
from openxml (@idoc, '/content/document/image', 2)  -- read elements
with 
(
    imageID int,
    imageTypeID int,
    notes varchar(1000),
    editorID int0
)

Any way round this?

Cheers,
Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 10:26:24

The first code example will work just fine.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-11 : 10:27:39

quote:
I assume this is going wrong because I'm trying to mix variables, constants and other fixed values with column values coming out of the temp table.

No, there is no such limitation.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources