TSQL and Autogenerate **SOLVED**

[Sgt-Peppa]

Hi all, I am pretty new to that TSQL thingy. Herers one thing I just cant seem to find an answer on the net!
I use MS SQL Server 2000.
This is what I do to create a new Table:

CREATE TABLE blah
(
blahId integer Primary Key Not Null,
myText varchar(50)
)
Go

This works fine. But I want to tell the SQLServer that the blahId will be autogenerated by the Database. What would I need to include in my script. I tried generate and autogenerate but both dont seem to work!

Also how would my insert Statement look like.
Usually you would like this:

INSERT INTO blah VALUES (NULL,'someText')

Will that still work?

Thanks,

Stephan

[zombie_man23]

I don't know anything about the scripting deal, but I can tell you that the insert statement won't work.

You set your blahID as the primary key, not null. And you are trying to set your PK as a null value, or at least a non-integer from what you typed. So you can't do that.

The blahID has to have an integer value in it for the insert statement to work.

-Side note:

And I believe you would just need:
insert into blah(mytext)
values('some text value')

If you get your PK field to autoincrement, because the blahID would automatically insert a new value upon insert of the mytext field.

[vb_dba]

Make your blahID field an Identity column:

CREATE TABLE blah
(
blahId integer Identity(1, 1) Primary Key Not Null,
myText varchar(50)
)
Go

Then you just use your other fields for insert:

Insert Into blah (myText) Values ('myText')

blahId will automatically be assigned the next number in the sequence.

[techgnome]

Just to expand a little regarding Identity(1, 1) .....
The first 1 is the seed. This sets what the next number in the sequence will be. Typicaly this is 1, so that the first item added gets the number 1.
The second one is the incrementor. This is added to the last number to get the next one in the sequence.

So, Identity(1, 1) results in 1,2,3,4,5..... while Identity(10, 1) gives you 10,11,12,13,14..... and Identity(10, 10) gives you 10,20,30,40,50, and so on.

Tg

[Sgt-Peppa]

Thanks to all of you! I am soooo bad at this SQl Server stuff!
Saved my b....... tonight!

Thanks again,

Stephan

[szlamany]

I am so happy to see another person who likes to script an object...

We do all our database work with scripts - store the scripts in source safe - and can easily create a new customer with these scripts...

At any rate - here's a version of a script like you asked about...

Use AcctFiles
GO
DROP TABLE PayDist_T
Go

CREATE TABLE PayDist_T
(PayEntry	int		not null identity
,PayStat	varchar(1)	null
,PayFiscalYr	varchar(5)	null
,PayDate	varchar(9)	null
,PayAcct	varchar(47)	null
,PayTDate	varchar(14)	null
,PayAmount	varchar(16)	null
,PayDesc	varchar(26)	null
,TDate		datetime
,constraint	    PKPayDist
		    PRIMARY KEY (PayEntry)
)
GO

We prefer to put our index constraints at the bottom of the CREATE TABLE - so we can name them specifically. That way when an error is returned by ADO we know what caused it - such as PKPayDist, or for a foreign key, FKPayDistFiscalYr. These names mean more to the client program then the odd names chosen by SQL...

BTW - this table is used to store legacy data that is imported once a week - that's why the odd datatypes for things like dates - pay no attention to that!

[techgnome]

I am so happy to see another person who likes to script an object...

We do all our database work with scripts - store the scripts in source safe - and can easily create a new customer with these scripts...

[snip]...[/snip]

We prefer to put our index constraints at the bottom of the CREATE TABLE - so we can name them specifically. That way when an error is returned by ADO we know what caused it - such as PKPayDist, or for a foreign key, FKPayDistFiscalYr. These names mean more to the client program then the odd names chosen by SQL...

That's the way we do it too.... and quite frankly I (or the rest of us here) wouldn't have it any other way. It's just sooo much easier.

Tg