auto generate id - 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

General SQL Server Forums

New to SQL Server Programming

auto generate id

Author

Topic

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-21 : 23:47:17

Hi,
i have two table, Customer(auto generate) and car. Customer table have customer name and both have Customer ID.
i want pass the customer id from customer to car once customer name is insert.

here is my code:
create table Customers
(
dbID int identity not null primary key,
CustomerName varchar(100)
)
Go

create function CustomerNumber (@id int)
returns char(5)
as
begin
return 'C' + right('0000' + convert(varchar(10), @id), 4)
end
Go

alter table Customers add CustomerNumber as dbo.CustomerNumber(dbID)
Go

INSERT INTO Customers(CustomerName) VALUES ('Name')
INSERT INTO Car(CustomerID) VALUES (SCOPE_IDENTITY())
Go

In this case, since the dbid is primary key so it will pass the dbid to car.but any idea that can pass the CustomerNumber from Customer table to car???because i want the Customer ID in car table is C0001 something like that...
is it possible???
or any other idea can do it???
thanks....

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-22 : 00:15:19

If you are using SQL 2005 or above, the INSERT statement has an OUTPUT clause that you can use to capture the data that was inserted.

You can see examples in SQL Server 2005 or 2008 Books Online.

CODO ERGO SUM

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 03:05:42

DECLARE @customer_id VARCHAR(50)

INSERT INTO Customers(CustomerName) VALUES ('Name')

SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Car(CustomerID) VALUES (@customer_id)

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 03:23:43

quote:
Originally posted by rohitvishwakarma

DECLARE @customer_id VARCHAR(50)

INSERT INTO Customers(CustomerName) VALUES ('Name')

SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Car(CustomerID) VALUES (@customer_id)

thanks...i get what i expect but why just one row only???
the record pass to car table just c0001 only...

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 03:40:10

quote:

thanks...i get what i expect but why just one row only???
the record pass to car table just c0001 only...

you mean to say even you are inserting number of customers, @customer_id is always c0001?
Ideally it should be following:

when you will insert the first customer, (@customer_id = c0001
when you will insert the second customer, (@customer_id = c0002
when you will insert the third customer, (@customer_id = c0003

make dbID IDENTITY(1,1)

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 03:48:11

quote:
Originally posted by rohitvishwakarma

quote:

thanks...i get what i expect but why just one row only???
the record pass to car table just c0001 only...

you mean to say even you are inserting number of customers, @customer_id is always c0001?
Ideally it should be following:

when you will insert the first customer, (@customer_id = c0001
when you will insert the second customer, (@customer_id = c0002
when you will insert the third customer, (@customer_id = c0003

make dbID IDENTITY(1,1)

Thanks for reply first...
the @customer_id not always c0001, but is just show record of c0001 only, means that c0002, c0003.....won't display in car table....

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 03:57:36

Suppose after 5 INSERTS
You write

SELECT * FROM Car

It shows customerId c0001 in all rows?

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:05:36

I have tested the same and getting following o/p

DECLARE @customer_id VARCHAR(50)
-----------FIRST INSERT
INSERT INTO Customers(CustomerName) VALUES ('FirstCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0001
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------SECOND INSERT
INSERT INTO Customers(CustomerName) VALUES ('SecondCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0002
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------THIRD INSERT
INSERT INTO Customers(CustomerName) VALUES ('ThirdCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0003
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------FOURTH INSERT
INSERT INTO Customers(CustomerName) VALUES ('FourthCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0004
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------FIFTH INSERT
INSERT INTO Customers(CustomerName) VALUES ('FifthCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0004
INSERT INTO Car(CustomerID) VALUES (@customer_id)

SELECT * FROM Customers

dbID Customername CustomerNumber
1 FirstCustomer c0001
2 SecondCustomer c0002
3 ThirdCustomer c0003
4 FourthCustomer c0004
5 FifthCustomer c0005

SELECT * FROM Car

CustomerId
c0001
c0002
c0003
c0004
c0005

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 04:09:35

quote:
Originally posted by rohitvishwakarma

Suppose after 5 INSERTS
You write

SELECT * FROM Car

It shows customerId c0001 in all rows?

means that, everytime i execute the declare and change the name, the data is work fine in car table.
but if i x execute the declare, but just direct insert the name in customer table, the customer id in customer table will increase like usual, but i won't pass the value to car table.

senario:
if i use
DECLARE @customer_id VARCHAR(50)

INSERT INTO Customers(CustomerName) VALUES ('Name')

SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Car(CustomerID) VALUES (@customer_id)
** the red color part i change name everytime, it will pass the value to car table.
for example i get c0006 and it will pass to car table.

but if i just direct type the name at customername column in customer table, although will appear c0007 at customer table but c0007 will not pass to car table.

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 04:11:01

quote:
Originally posted by rohitvishwakarma

I have tested the same and getting following o/p

DECLARE @customer_id VARCHAR(50)
-----------FIRST INSERT
INSERT INTO Customers(CustomerName) VALUES ('FirstCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0001
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------SECOND INSERT
INSERT INTO Customers(CustomerName) VALUES ('SecondCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0002
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------THIRD INSERT
INSERT INTO Customers(CustomerName) VALUES ('ThirdCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0003
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------FOURTH INSERT
INSERT INTO Customers(CustomerName) VALUES ('FourthCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0004
INSERT INTO Car(CustomerID) VALUES (@customer_id)

-----------FIFTH INSERT
INSERT INTO Customers(CustomerName) VALUES ('FifthCustomer')
SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()
PRINT @customer_id -- it should be c0004
INSERT INTO Car(CustomerID) VALUES (@customer_id)

SELECT * FROM Customers

dbID Customername CustomerNumber
1 FirstCustomer c0001
2 SecondCustomer c0002
3 ThirdCustomer c0003
4 FourthCustomer c0004
5 FifthCustomer c0005

SELECT * FROM Car

CustomerId
c0001
c0002
c0003
c0004
c0005

ya..i also get this...
but if direct giva name for cuatomername in customer table,it won't pass to car table...

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:21:40

quote:
Originally posted by yvette

but if i just direct type the name at customername column in customer table, although will appear c0007 at customer table but c0007 will not pass to car table.

you mean to say :
When you execute the following query

DECLARE @customer_id VARCHAR(50)

INSERT INTO Customers(CustomerName) VALUES ('Name')

SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Car(CustomerID) VALUES (@customer_id)

Tt works fine.

But when you just Insert the Customer name in Customers table (using GUI ? Right Click on Tablename > Edit All Rows) the id is proper in Customers table but not in Car. If that is the case, firstly you are not inserting anything in Car table, how do you expect to see the id in Car table?

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:21:59

quote:
Originally posted by yvette

but if i just direct type the name at customername column in customer table, although will appear c0007 at customer table but c0007 will not pass to car table.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:24:14

I think you want the CustomerID's to be inserted into car table as soon as a new entry has been made to the Customer Table. In that case what you need is a TRIGGER on Customer table.

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 04:26:16

quote:
Originally posted by rohitvishwakarma

quote:
Originally posted by yvette

but if i just direct type the name at customername column in customer table, although will appear c0007 at customer table but c0007 will not pass to car table.

you mean to say :
When you execute the following query

DECLARE @customer_id VARCHAR(50)

INSERT INTO Customers(CustomerName) VALUES ('Name')

SELECT @customer_id = CustomerNumber FROM Customers WHERE dbId = SCOPE_IDENTITY()

INSERT INTO Car(CustomerID) VALUES (@customer_id)

Tt works fine.

But when you just Insert the Customer name in Customers table (using GUI ? Right Click on Tablename > Edit All Rows) the id is proper in Customers table but not in Car. If that is the case, firstly you are not inserting anything in Car table, how do you expect to see the id in Car table?

oh...i think that it should be pass to car table...
sorry for that...
and thanks to solve my problem...
thanks....

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 04:28:44

i have another question.
How i create the created Date and Last updated Date in table???

Kristen
Test

22859 Posts

Posted - 2010-09-22 : 04:35:00

We have CreateDate column that is added by a DEFAULT constraint.

We set the UpdateDate in the UPDATE command (in a Stored Procedure), however you could set it using a Trigger on the table.

We do NOT use a Trigger because sometime we want to insert rows from another source WITHOUT changing the UpdateDate - i.e. the rows retain the UdpateDate from the Source table they came from.

But YMMV!

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:42:13

quote:
Originally posted by yvette

i have another question.
How i create the created Date and Last updated Date in table???

ALTER TABLE [dbo].[Customers] ADD [CreatedDate] DATETIME
GO
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [DF_Customers_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Customers] ADD [UpdatedDate] DATETIME
GO

the CreatedDate column will get filled automatically with the current datetime when a new record will be inserted into the Customers table
and you can set the UpdatedDate whenever you are performing the Updates on the table

like:
UPDATE Customers
SET customername = 'newName',
UpdatedDate = GETDATE()
WHERE dbID = 5

yvette
Yak Posting Veteran

74 Posts

Posted - 2010-09-22 : 04:51:00

quote:
Originally posted by rohitvishwakarma

quote:
Originally posted by yvette

i have another question.
How i create the created Date and Last updated Date in table???

ALTER TABLE [dbo].[Customers] ADD [CreatedDate] DATETIME
GO
ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [DF_Customers_CreatedDate] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Customers] ADD [UpdatedDate] DATETIME
GO

the CreatedDate column will get filled automatically with the current datetime when a new record will be inserted into the Customers table
and you can set the UpdatedDate whenever you are performing the Updates on the table

like:
UPDATE Customers
SET customername = 'newName',
UpdatedDate = GETDATE()
WHERE dbID = 5

is it means that i need to update the updateddate manually everytime???

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-22 : 04:59:55

quote:
Originally posted by yvette

is it means that i need to update the updateddate manually everytime???

Aren't you going to create a Stored procedure for doing the updates

The GetDate() will put the Updated time in the table whenever you will call the Stored procedure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources