SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 extract value from select		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Yak Posting Veteran

72 Posts
Posted - 02/26/2013 :  08:24:07  Show Profile  Reply with Quote
Hello,
I have the following:

INSERT INTO TABLEB VALUES(col1, col2, col3,.....)
SELECT col1, col2, col3,.... FROM TABLEA

I want to insert col1 value into a log table as well so can
I extract col1 into @col1?

Thanks.

bandi
Flowing Fount of Yak Knowledge

India
1451 Posts
Posted - 02/26/2013 :  08:34:44  Show Profile  Reply with Quote
Means you want to insert col1 of TABLEA to TABLEB table...?

INSERT INTO TABLEB VALUES(col1)
SELECT col1 FROM TABLEA

If TABLEA is existed in another Database, you can use fully qualified name like

INSERT INTO TABLEB VALUES(col1)
SELECT col1 FROM DatabaseName.dbo.TABLEA


--
Chandu
Go to Top of Page

Ifor
Constraint Violating Yak Guru

476 Posts
Posted - 02/26/2013 :  08:40:50  Show Profile  Reply with Quote 

INSERT INTO TableB (col1, col2, col3,.....)
OUTPUT inserted.Col1 INTO LogTable(Col1)
SELECT col1, col2, col3,....
FROM TableA;
Go to Top of Page

rama108
Yak Posting Veteran

72 Posts
Posted - 02/26/2013 :  14:11:54  Show Profile  Reply with Quote
What is :
OUTPUT inserted.Col1 INTO LogTable(Col1)

it complains about the word "inserted"

Thanks.
Go to Top of Page

rama108
Yak Posting Veteran

72 Posts
Posted - 02/26/2013 :  14:30:02  Show Profile  Reply with Quote
I am getting the following when i run the query with output inserted:

A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.
Thanks.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 02/26/2013 :  15:13:36  Show Profile  Reply with Quote
Then either tableB is on another server, or your query has a derived table in it. Either way, you can't use the OUTPUT operator. If it's a derived table you can make that a temp table and you'll be okay, but if TableB or LogTable is on another server, you're out of luck. You'll just have to do two different INSERT INTO statements.


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

rama108
Yak Posting Veteran

72 Posts
Posted - 02/26/2013 :  15:25:53  Show Profile  Reply with Quote
Thank you Jim.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000