unique identifier - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

unique identifier

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-10 : 15:56:16

How do I insert the unique identifier into a table? I have this, but it doesn't work:

INSERT INTO tblEmail (UniqueID, CaseNumber, PartNumber) SELECT * FROM tblCapRec

Thanks!

Brenda

If it weren't for you guys, where would I be?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 15:59:52

don't use SELECT * -- explicitly indicate which columns to pull from your table. they must be listed in the same order specified in your INSERT statement. Otherwise how does SQL Server know which column from the SELECT to put in which column in the destination table?

Read more in books on-line about INSERT.

- Jeff

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-10 : 16:02:55

I know * wouldn't work for that, but I don't know what do to for a unique identifier column. I have never had to do that before. Thanks!

Brenda

If it weren't for you guys, where would I be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-10 : 16:25:11

NewId() will give you a unique identifier.

INSERT INTO tblEmail (UniqueID, CaseNumber, PartNumber)
SELECT NewId(), CaseNumber, PartNumber FROM tblCapRec

Go with the flow & have fun! Else fight the flow

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-10 : 16:28:04

will newID() start with 1? Or does it just pick whatever number?

Brenda

If it weren't for you guys, where would I be?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 16:33:59

????

you just declare an IDENTITY on your table when you create it. then whenever a row is added, one is assigned. You EXCLUDE that column in your INSERT statements, since it is handled by default.

This is not something you need to accomodate during an INSERT statment -- it's a one-time thing done during your table creation process. From there, you just ignore the column.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-10 : 16:35:28

Brenda, you are confused about what a unique identifier is. Run SELECT NEWID() in Query Analyzer to see what it looks like. It's not an incrementing integer.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-11 : 12:10:27

I am trying to insert records that have a payment due date into a different table. This is what I have so far:


declare @countID int
declare @counter int
declare @length int

	set @counter = 0
	set @length = (select count(paymentdue) from tblcaprec where paymentdue = convert(varchar(8),getdate()-1,112))
	set @countID = @length

	while @counter < @length
	begin

	INSERT INTO tblEmail (UniqueID, CaseNumber, PartNumber) SELECT @countID, CaseNumber, PartNumber FROM tblCapRec
	WHERE PaymentDue = convert(varchar(8),getdate()-1,112)
	
	
	set @counter = @counter + 1
	set @countID = @countID - 1
		
	end

It isn't working though. It inserts 4 records for each Unique ID. There are 4 records total that need to be inserted, but right now it is inserting 16 records. Any ideas on how to fix that? Thanks!

Brenda

If it weren't for you guys, where would I be?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-11 : 12:14:34

why are you doing all this??? your uniqueID column just needs to be declared as an IDENTITY and then that's it -- you ingore it. SQL Server takes care of incrementing the value whenever you add a row.

there is no need for a loop after that is done; you just add your rows to the table, and as we said over and over, from that point you just ignore the ID column:

INSERT INTO tblEmail (~~UniqueID,~~ CaseNumber, PartNumber)
SELECT ~~@countID,~~ CaseNumber, PartNumber FROM tblCapRec
WHERE PaymentDue = convert(varchar(8),getdate()-1,112)

That's it! one statement, no loop needed.

Does this make sense?

- Jeff

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-11 : 12:19:56

What do you mean declared as an IDENTITY? That isn't an option that I see. I see uniqueidentifier, but that doesn't work. What should I do?

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-11 : 12:54:18

I figured it out! Thanks for everything...again!

CREATE TABLE tblEmail
(UniqueID smallint identity(1,1),
CaseNumber char(10) )

Brenda

If it weren't for you guys, where would I be?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources