CTAS with Link Server - 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

General SQL Server Forums

New to SQL Server Programming

CTAS with Link Server

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-06-07 : 02:19:38

Hello All,

Is it possible to use CTAS then add a column that doesnt exist from the table that I select?

I have use this syntax to Run Create Table As in which the table that I select is from a MYSQL Database.


use adventureworks2008r2;
go
select * 
into inv_mlb_001 
from openquery(LMYSQL, 'SELECT
`inv_mlb_001`.`mlb_Dte`,
`inv_mlb_001`.`mlb_Shf`,
`inv_mlb_001`.`MlB_ODR`,
`inv_mlb_001`.`MlB_Rte_ID`,
`inv_mlb_001`.`mlb_Ppr_ID`,
`inv_mlb_001`.`mlb_Ppr_Nme`,
`inv_mlb_001`.`mlb_Job`,
`inv_mlb_001`.`mlb_Mdl_nme`,
`inv_mlb_001`.`mlb_Sts`,
`inv_mlb_001`.`mlb_cur_Qty`,
`inv_mlb_001`.`mlb_Cnt`
FROM `mcgis_copr`.`inv_mlb_001`;')
go

Now my problem is I want to add this column (MLB_OID) that is auto_increment and will serve as Primary key.

How will I going to achieve this?

Thank you..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 04:15:04

have yoou tried select *, identity(int,1,1) as MLB_OID
into ...

p.s. CTAS is an oracle expression.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-06-08 : 02:06:13

Thank you Sir I works but how can I set the MLB_OID as the 1 column?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-08 : 06:00:07

You mean the first column? you shouldn't have to but

select identity(int,1,1) as MLB_OID, *
into ...

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources