Select and Insert - SQL Server Forums

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)

Select and Insert

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-01-30 : 16:03:15

I am connecting to a db2/400 data base and trying to insert result into a sequel server table:

insert into ZyTax.dbo.xtable(report_state, report_year, report_month, company) 
values(ztadds, ztyear, zt#mt, ztco)
exec('Select ztadds, ztyear, zt#mt, ztco from QTEMP.' + @File ) at AS400SRV_MSDASQL

Get error:
The name "ztadds" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-30 : 16:05:48

You can't use VALUES.

INSERT INTO Table1 (...)
EXEC ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-30 : 23:34:02

First execute the sp and get those values into a cursor or something else like a temp table and then try to implement the INSERT statement.
:)

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-31 : 00:41:07

Do not use a cursor. Put the data into a table via INSERT/EXEC as already mentioned.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-31 : 00:52:04

Thats what i have said cursor or something else like a temp table tkizer.

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:27:43

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources