Remove From My Forums

IDENTITY column with OPENXML insert

Question

Hi all,

I'm using SELECT INTO with OPENXML but I can't specify an IDENTITY column in the WITH clause, I keep getting "Incorrect syntax near the word "IDENTITY". The XML doc doesn't contain the ID value I want to use.

SELECT xml.a, xml.b, xml.c,
	xml.d, xml.e, xml.f, 
	xml.g, xml.h, xml.i
INTO dbo.table
FROM OPENXML(@nDoc, '/level1/level2/level3', 2)
WITH (
	TableID int IDENTITY(1,1),
	a int 'level4/level5',
	b int 'level4/level6',
	c int 'level4/level7',
	d int 'level4/level8',
	e int 'level4/level9',
	f int 'level4/level10',
	g int 'level4/level11',
	h int 'level4/level12',
	i int 'level4/level13'
 ) xml

Any ideas?

Thursday, June 2, 2011 3:51 PM

Answers

Put the IDENITY function in the outer SELECT and specify the data-type you want the column to be, eg:

SELECT 
	IDENTITY(INT, 1,1) as TableID,
	xml.a, xml.b, xml.c,
	xml.d, xml.e, xml.f, 
	xml.g, xml.h, xml.i
INTO dbo.[table]
FROM OPENXML(@nDoc, '/level1/level2/level3', 2)
WITH (
	a int 'level4/level5',
	b int 'level4/level6',
	c int 'level4/level7',
	d int 'level4/level8',
	e int 'level4/level9',
	f int 'level4/level10',
	g int 'level4/level11',
	h int 'level4/level12',
	i int 'level4/level13'
 ) xml

Proposed as answer by wBob Thursday, June 2, 2011 5:10 PM
Unproposed as answer by HunchbackMVP Thursday, June 2, 2011 5:10 PM
Proposed as answer by HunchbackMVP Thursday, June 2, 2011 5:11 PM
Marked as answer by Ashish Khatri Tuesday, June 7, 2011 5:29 PM

Thursday, June 2, 2011 5:10 PM

Answered by:

IDENTITY column with OPENXML insert

Question

Answers