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 Columns Expand / Collapse
Author
Message
Posted Monday, July 7, 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: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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
Posted Monday, July 7, 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: Wednesday, September 3, 2014 11:26 PM
Points: 3,214, Visits: 2,336
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
Posted Monday, July 7, 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
Posted Monday, July 7, 2008 10:30 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:21 PM
Points: 117, Visits: 572
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
Posted Tuesday, July 8, 2008 9:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,363, Visits: 1,391
Excellent one.......:)


Post #530124
Posted Tuesday, July 8, 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: Yesterday @ 4:12 PM
Points: 3,475, Visits: 582
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
Posted Sunday, August 1, 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, September 2, 2014 8:29 AM
Points: 697, Visits: 355
Just spelling changes?
Post #961870
Posted Wednesday, August 27, 2014 3:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 2,851, Visits: 232
Interesting. Now to find a use.
Post #1608062
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse