Using With in a 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)

Using With in a insert

Author

Topic

Thedish
Starting Member

2 Posts

Posted - 2009-11-05 : 10:21:27

Hi
I'm new to TSQL but have been an oracle develper for quite a few years. I have a new job to migrate code from Oracle to SQL Server 2005. Most things are okay but when I try to use a WITH at the start o a cursor it doesn't like it. Any help would be appreciated.
The syntax is something like this but a lot more joins and in line views etc

insert into table1(col1
,col2
,col3)
with list AS (SELECT col1
FROM table2
WHERE 1 =1)
SELECT col2
,col3
where col4 = list.col1

Thanks
Dave

Dave C

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-11-05 : 10:37:48

start out as such


WITH cteList(col1)
  AS 
(SELECT col1
   FROM table2
  WHERE 1 =1) 
INSERT INTO Table1
 (col1)
SELECT col1 from 
  FROM cteList

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Thedish
Starting Member

2 Posts

Posted - 2009-11-05 : 11:00:58

Thank you i'll give it a go.

quote:
Originally posted by yosiasz

start out as such

WITH cteList(col1)
AS
(SELECT col1
FROM table2
WHERE 1 =1)
INSERT INTO Table1
(col1)
SELECT col1 from
FROM cteList

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Dave C

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-05 : 12:19:33

And don't forget to put a ";" before the WITH !

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources