SQL SERVER: How to generate auto ID when inserting bulk data into table

Question

SQL SERVER: How to generate auto ID when inserting bulk data into table

T.Zacks 3,996

see my sql.
declare @XML xml=N'<?xml version="1.0" encoding="utf-16"?>
<root>
<SectionLineItemDto>
<Section>Model Output</Section>
<LineItem>Products</LineItem>
</SectionLineItemDto>
<SectionLineItemDto>
<Section>Test Output</Section>
<LineItem>Test Products</LineItem>
</SectionLineItemDto>
</root>'

	SELECT 'AAPL',Section,'I' AS Action,  
	(Select ROW_NUMBER() OVER (PARTITION BY SectionID ORDER BY InsertedOn DESC) FROM tblSectionTemplate WHERE TickerID='AAPL') RowNum,  
	GetDate() AS InsertedOn,'ADM' AS InsertedBy FROM  
	(  
		SELECT d.v.value('(Section/text())[1]', 'VARCHAR(MAX)') AS Section  
		FROM @xml.nodes('/root/SectionLineItemDto') AS d(v)  
	) X

When select query execute then ROW_NUMBER() function returning null. but i want it should return 1 for first records. 2 for second records etc.

where my is my mistake for ROW_NUMBER() not returning auto id. thanks

Yitzhak Khabinsky 26,586 Reputation points

2022-03-16T12:43:31.507+00:00

@T.Zacks ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

Accepted answer

1 additional answer

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2022-03-16T12:43:31.507+00:00

@T.Zacks ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

Answer 1

Jack J Jun 25,296

@T.Zacks , Welcome to Microsoft Q&A, based on my test, I reproduced your problem.

I tried the following code and it could return auto id.

SELECT 'AAPL',Section,'I' AS Action,  
       ROW_NUMBER() OVER ( PARTITION BY SectionID ORDER BY InsertedOn DESC) RowNum,  
     GetDate() AS InsertedOn,'ADM' AS InsertedBy FROM  
     (  
         SELECT d.v.value('(Section/text())[1]', 'VARCHAR(MAX)') AS Section  
         FROM @xml.nodes('/root/SectionLineItemDto') AS d(v)  
     ) X  
     inner join tblSectionTemplate on tblSectionTemplate.TickerID='AAPL'

Result:

Best Regards,
Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

T.Zacks 3,996 Reputation points

2022-03-18T17:41:51.16+00:00
I have done the job this way.
declare @XML xml=N'<?xml version="1.0" encoding="utf-16"?>
<root>
<SectionLineItemDto>
<SectionID>0</SectionID>
<Section>Model Output</Section>
<LineItem>Products</LineItem>
</SectionLineItemDto>
<SectionLineItemDto>
<SectionID>0</SectionID>
<Section>Test Output</Section>
<LineItem>Test Products</LineItem>
</SectionLineItemDto>
</root>'

SELECT 'AAPL',Section,'I' AS Action, ROW_NUMBER() OVER ( PARTITION BY SectionID ORDER BY (select 1) DESC) RowNum, 'ADM' AS InsertedBy FROM ( SELECT d.v.value('(Section/text())[1]', 'VARCHAR(MAX)') AS Section, d.v.value('(SectionID/text())[1]', 'INT') AS SectionID FROM @xml.nodes('/root/SectionLineItemDto') AS d(v) ) X

Answer 2

Naomi Nosonovsky 8,431

Does this work?

SELECT 'AAPL',Section,'I' AS Action,
 ROW_NUMBER() OVER (PARTITION BY SectionID ORDER BY (select 1) DESC) as  RowNum,
     GetDate() AS InsertedOn,'ADM' AS InsertedBy FROM
     (
         SELECT d.v.value('(Section/text())[1]', 'VARCHAR(MAX)') AS Section
         FROM @xml.nodes('/root/SectionLineItemDto') AS d(v)
     ) X

Share via

SQL SERVER: How to generate auto ID when inserting bulk data into table

1 additional answer

Your answer