INSERT INTO... SELECT... and OUTPUT

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)

INSERT INTO... SELECT... and OUTPUT

Author

Topic

Disco Patrick
Starting Member

18 Posts

Posted - 2009-08-04 : 07:30:06

Having a problem writing a query where I insert into a table by selecting from another table, and also outputting values into another table at the same time.

Here's what I have so far:


INSERT INTO RT(Name, ShortName, SeriesID)
SELECT Name, ShortName, SeriesID
FROM RT
INNER JOIN RTinM
ON RT.ID = RTinM.RTID
WHERE RTinM.MID = 1
OUTPUT inserted.ID INTO #temp

I have tried placing the OUTPUT line in different places, but can't seem to get it to work. I'm sure there must be a different syntax i need to use here.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 07:39:28

See here for how to write queries using OUTPUT operator
http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx

N 56°04'39.26"
E 12°55'05.63"

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-04 : 07:40:54

Declare @tmp table(ID int)
INSERT INTO RT(Name, ShortName, SeriesID)
OUTPUT inserted.SeriesID INTO @tmp
SELECT Name, ShortName, SeriesID
FROM RT
INNER JOIN RTinM
ON RT.ID = RTinM.RTID
WHERE RTinM.MID = 1

--------------------
Rock n Roll with SQL

Disco Patrick
Starting Member

18 Posts

Posted - 2009-08-04 : 08:12:18

thanks rocknpop, I think that was the one combination i didn't try :)

EDIT: arrrrgh... no... I *have* already tried it... it doesn't work. Well, it doesn't work when using a #temp table anyway. I'll try using a table variable like you have done.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 08:25:00

If course it works with temp tables!
You have to use the right column name...

CREATE TABLE	#Copy
		(
			i INT
		)

CREATE TABLE	#Source
		(
			a INT
		)

INSERT	#Source
SELECT	100

CREATE TABLE	#Target
		(
			z INT
		)

INSERT	#Target
OUTPUT	inserted.z
INTO	#Copy
SELECT	a
FROM	#Source

SELECT	*
FROM	#Copy

SELECT	*
FROM	#Target

DROP TABLE	#Copy,
		#Source,
		#Target

N 56°04'39.26"
E 12°55'05.63"

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-08-04 : 08:42:13

The only point to note here is that the table should already be there, OUTPUT clause does not create a table dynamically.
So you can use both temp and table variable.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources