Insert Multiple rows with a select statement

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 2008 Forums

Transact-SQL (2008)

Insert Multiple rows with a select statement

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2011-06-09 : 21:07:15

Hi All,

i want to prepare a insert statement with a select from a different table where i need to insert multiple rows, the problem is the id is primary key and i need to run a query to auto increment the id.

i have written a query below but it will only increment to 1 i.e. from 222 to 223 what i want is it should auto increment to 223,224,225,226 and onwards.

following is my query:
insert into opportunity (OPPO_OPPORTUNITYID,OPPO_PRIMARYpersonid,oppo_description)
SELECT ( select MAX(t.Oppo_opportunityId)+1 from opportunity t)as MAX_ID, dbo.Person.Pers_PersonId,'test'
FROM dbo.Person INNER JOIN
dbo.Opportunity ON dbo.Person.Pers_PersonId = dbo.Opportunity.Oppo_PrimaryPersonId
where pers_createddate between '01-JAN-2000' and '31-MAR-2011'
GROUP BY dbo.Opportunity.Oppo_OpportunityId, dbo.Person.pers_PersonId

please help.

regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-10 : 01:47:50

[code]declare @offset int
select @offset = isnull(MAX(Oppo_opportunityId),0) from opportunity

insert into opportunity (OPPO_OPPORTUNITYID,OPPO_PRIMARYpersonid,oppo_description)
select * from
(
SELECT
row_number() over (order by dbo.Person.Pers_PersonId) + @offset as OPPO_OPPORTUNITYID,
dbo.Person.Pers_PersonId as OPPO_PRIMARYpersonid,
'test' as oppo_description
FROM dbo.Person INNER JOIN
dbo.Opportunity ON dbo.Person.Pers_PersonId = dbo.Opportunity.Oppo_PrimaryPersonId
where pers_createddate between '01-JAN-2000' and '31-MAR-2011'
GROUP BY dbo.Opportunity.Oppo_OpportunityId, dbo.Person.pers_PersonId
)dt
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

ppatel112
Starting Member

35 Posts

Posted - 2011-06-10 : 02:27:34

Thanks a lot worked a charm!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources