Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

identity table use case Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 3:14 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,801, Visits: 257
SQL 2008R2

How to insert / retrieve 3 identity values without using GO 3 ??
Also insert top(3) ... does not work either ...
Please see the snippet below:

create table N (i int not null identity(1,1) constraint PK_ primary key);
create table #tmp (i int not null primary key);
GO
-- run these 2 inserts 3 times
insert dbo.N default values;
insert #tmp values(scope_identity());
Go 3

select * from #tmp
GO
-- clean up
drop table N
drop table #tmp
GO
Post #1413357
Posted Tuesday, January 29, 2013 3:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204
vlad-548036 (1/29/2013)
SQL 2008R2

How to insert / retrieve 3 identity values without using GO 3 ??
Also insert top(3) ... does not work either ...
Please see the snippet below:

create table N (i int not null identity(1,1) constraint PK_ primary key);
create table #tmp (i int not null primary key);
GO
-- run these 2 inserts 3 times
insert dbo.N default values;
insert #tmp values(scope_identity());
Go 3

select * from #tmp
GO
-- clean up
drop table N
drop table #tmp
GO


Easiest way is by using OUTPUT. http://msdn.microsoft.com/en-us/library/ms177564.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1413365
Posted Tuesday, January 29, 2013 3:48 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,801, Visits: 257
Sean, please post the working sql if you have it. I tried using output clause and still unable to output 3 identity numbers which is the goal here.
Post #1413369
Posted Tuesday, January 29, 2013 3:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:09 PM
Points: 5,658, Visits: 6,100
+1 to Sean's recommendation, OUTPUT is the way to go for this from SQL 2k5+.

The earlier alternatives are very, very messy and require a lot of coding to make your own sequence components.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1413373
Posted Tuesday, January 29, 2013 4:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763
insert dbo.N
output inserted.$identity into #tmp ( i )
default values

go 3

select *
from #tmp


SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1413381
Posted Tuesday, January 29, 2013 4:13 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,801, Visits: 257
you are still using the GO 3 method.
The question is how to do it without GO 3 ??
Post #1413382
Posted Tuesday, January 29, 2013 4:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,801, Visits: 257
btw, Thank you for showing how to use output clause in combination with default values, as it is not something we run into everyday!
Post #1413385
Posted Tuesday, January 29, 2013 5:26 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:23 PM
Points: 294, Visits: 1,103
Hi

Without the GO 3 and using either values or select

create table N (i int not null identity(100,1) constraint PK_ primary key,test bit);
create table #tmp (i int not null primary key);
GO
-- run these 2 inserts 3 times
insert into dbo.N (test) OUTPUT INSERTED.i INTO #tmp (i) values (1),(1),(1) ;
insert into dbo.N (test) OUTPUT INSERTED.i INTO #tmp (i) SELECT test FROM (values (1),(1),(1)) AS n(test) ;
Go

select * from #tmp
GO
-- clean up
drop table N
drop table #tmp
GO

Post #1413396
Posted Wednesday, January 30, 2013 12:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:51 PM
Points: 1,801, Visits: 257
Thanks, that's what I initially had on my system. It is apparent now, that there is no way

declare @howMany int=3
insert top (@howMany)

can be used to force insertion of more than 1 row and return keys back.

Thank you!
Post #1413741
Posted Wednesday, January 30, 2013 2:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,318, Visits: 1,763
vlad-548036 (1/30/2013)
Thanks, that's what I initially had on my system. It is apparent now, that there is no way

declare @howMany int=3
insert top (@howMany)

can be used to force insertion of more than 1 row and return keys back.

Thank you!



Not with "DEFAULT VALUES".

It's easy to do outside of that.


insert into dbo.tablename ( ...col_list... )
select ...col_list...
from dbo.source_table
cross join (
select top (@howMany) 1
from sys.objects
) AS insert_repeater


SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1413771
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse