January 29, 2013 at 3:14 pm
SQL 2008R2
How to insert / retrieve 3 identity values without using GO 3 ??:-)
Also insert top(3) ... does not work either ... :w00t:
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
January 29, 2013 at 3:41 pm
vlad-548036 (1/29/2013)
SQL 2008R2How to insert / retrieve 3 identity values without using GO 3 ??:-)
Also insert top(3) ... does not work either ... :w00t:
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2013 at 3:48 pm
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.
January 29, 2013 at 3:53 pm
+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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 29, 2013 at 4:09 pm
insert dbo.N
output inserted.$identity into #tmp ( i )
default values
go 3
select *
from #tmp
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 29, 2013 at 4:13 pm
you are still using the GO 3 method.:cool:
The question is how to do it without GO 3 ??
January 29, 2013 at 4:19 pm
btw, Thank you for showing how to use output clause in combination with default values, as it is not something we run into everyday!
January 29, 2013 at 5:26 pm
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
January 30, 2013 at 2:29 pm
vlad-548036 (1/30/2013)
Thanks, that's what I initially had on my system. It is apparent now, that there is no waydeclare @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) A socialist is someone who will give you the shirt off *someone else's* back.
January 31, 2013 at 12:21 pm
vlad-548036 (1/30/2013)
Thanks, that's what I initially had on my system. It is apparent now, that there is no waydeclare @howmany int=3
insert top (@howMany)
can be used to force insertion of more than 1 row and return keys back.
Thank you!
While you be would much better off adding a valued column so that table N was defined as (This would give you a much cleaner code solution.)(i INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,b BIT)
However, ignoring the question of if you should, the statement above "there is no way", asks if you can. While the below solution is not elegant, it will achieve exactly what you're trying to achieve.
USE ScratchPad /* change to YOUR junk database */
-----------------------------------------------
-- prep tables
-----------------------------------------------
IF NOT(object_id(N'tempdb.dbo.##tmp') IS NULL) DROP TABLE ##tmp
IF NOT(object_id(N'N') IS NULL) DROP TABLE N
CREATE TABLE N (i INT IDENTITY(10,1) PRIMARY KEY CLUSTERED)
CREATE TABLE ##tmp (iIdentValue INT PRIMARY KEY CLUSTERED)
-----------------------------------------------
-- process
-----------------------------------------------
DECLARE @iValueCount INT
DECLARE @SQL VARCHAR(max)
SET @iValueCount = 3 /* Set this value to the number of "iterations" */
SET @SQL = 'DECLARE @SQL VARCHAR(max)'+char(10)
+ 'SET @SQL = ('+char(10)
+ 'SELECT TOP '+cast(@iValueCount AS VARCHAR)+char(10)
+ ' ''INSERT INTO dbo.N OUTPUT INSERTED.i INTO ##tmp DEFAULT VALUES''+char(10)'+char(10)
+ 'FROM sys.columns c WITH(NOLOCK)'+char(10)
+ 'CROSS JOIN sys.columns'+char(10)
+ 'FOR XML PATH('''')'+char(10)
+ ')'+char(10)
+ 'EXEC(@SQL)'
EXEC (@SQL)
SELECT *
FROM ##tmp
-
January 31, 2013 at 12:27 pm
Eh, dynamic sql ?. Anything is possible with dynamic sql, right?:-)
January 31, 2013 at 1:05 pm
OK this can be done without global temp tables and no dynamic sql. The real challenge is that you are trying to insert rows into a table that has only a single identity column. All you need to do is add another column and this becomes pretty simple. In the example below I simply add a column so there is a column besides the identity column to insert.
create table N (i int not null identity(1,1) constraint PK_ primary key);
create table #tmp (i int not null primary key);
GO
--Add a column so we have a real column for an insert.
alter table N
add TempCol char(1)
go
insert dbo.N (TempCol)
output inserted.$identity into #tmp(i)
select top 3 '' from sys.tables
--drop the extra column
alter table N
drop column TempCol
select * from #tmp
GO
-- clean up
drop table N
drop table #tmp
That of course leaves us to ask the question, why do you have a table that has nothing more than a single identity column?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 2:02 pm
Thank you for your input.
why do you have a table that has nothing more than a single identity column?
the sole purpose for it is to generate unique sequential numbers (IDs) to be used elsewhere in the system.
"Elsewhere" does not have capability to generate them, (IDs), there ... moreover in an environment where concurrent "subscribers" are possible, a single location, like an identity table, is necessary for uniqueness and sequencing.
January 31, 2013 at 2:16 pm
vlad-548036 (1/31/2013)
Thank you for your input.why do you have a table that has nothing more than a single identity column?
the sole purpose for it is to generate unique sequential numbers (IDs) to be used elsewhere in the system.
"Elsewhere" does not have capability to generate them, (IDs), there ... moreover in an environment where concurrent "subscribers" are possible, a single location, like an identity table, is necessary for uniqueness and sequencing.
I had a feeling that was the case. I have never understood the notion of keeping every ID for every table unique through the system but that is a discussion for another day. Adding a single column as a char(1) or a bit will make this sort of thing a LOT easier to deal with.
If you have a chance to upgrade to sql 2012 there is now a sequence object that is designed for doing this sort of thing.
http://msdn.microsoft.com/en-us/library/ff878091.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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply