Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Susantha Bathige » Identity Columns
Add to briefcase
17 posts, Page 2 of 2
««12

Identity Columns Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SanjayAttray
Posted Monday, July 07, 2008 11:36 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
Good one. Always thought insert with insert into..but never thought default values could be used to insert identity value for an identity column.

SQL DBA.
Post #529508
rudy - Doctor "X"
Posted Monday, July 07, 2008 11:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:11 PM
Points: 3,108, Visits: 2,114
Curte question ... a fairly obvious solution ...

However, why would one do such a thing ?



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #529516
Pires-502779
Posted Monday, July 07, 2008 12:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 16, 2010 3:52 AM
Points: 78, Visits: 55
rudy komacsar (7/7/2008)
Curte question ... a fairly obvious solution ...

However, why would one do such a thing ?


I can't see why we would have a table with only an identity, but "default values" is useful when you have a table with defined default values and you are creating data and the default values are suficient for the cenario.

I think the real lesson where is the "default values" for columns and not the trick for the identity insert.
Post #529542
SAinCA
Posted Monday, July 07, 2008 10:30 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:16 AM
Points: 115, Visits: 486
If you want to take a one-time hit of about 50 seconds (on my 2008 instance on a Lenovo T61p laptop) for 65000 rows in the ultra-useful "Numbers" table, use the simple Insert in the answer followed by GO 65000 to get an easy 65,000 rows... Drop and recreate with as many or few rows simply with a change to the GO count... Sure beats some of the other "how to populate a Numbers table" scripts for simplicity not performance :D

CREATE TABLE [dbo].[Numbers](
	[Number] [int] IDENTITY(0,1) NOT NULL,
 CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED ([Number] ASC)  ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.Numbers DEFAULT VALUES
go 65000
SELECT * FROM dbo.Numbers ORDER BY 1
go

As it's actually useful to have the Number 0 in the table - start the IDENTITY at ZERO - fell over that in Production - DUH!
Post #529751
Anipaul
Posted Tuesday, July 08, 2008 9:27 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 4,787, Visits: 1,335
Excellent one.......:)


Post #530124
Yelena Varshal
Posted Tuesday, July 08, 2008 10:24 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 30, 2012 10:42 AM
Points: 3,434, Visits: 519
SAinCA,

this is so neat!

I too was going to reply that this is very useful for NUMBERS table for the use with matrix - like operations (read " Inside Microsoft SQL Server 7.0 " (Microsoft Press) by Ron Soukup and Karen Delaney, this book has some realy good examples, don't mind the book is for 7.0, what they say works in any version)

What I did not know is that GO (Number) will execute as many times as the number specified. I was going to run the insert in the While loop.

Regards,
Yelena Varshal
Post #530190
SQL Writer
Posted Sunday, August 01, 2010 3:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 28, 2012 4:29 AM
Points: 688, Visits: 311
Just spelling changes?
Post #961870
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.