Generate a new ID which is not taken

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

Generate a new ID which is not taken

Author

Topic

ben53
Starting Member

2 Posts

Posted - 2013-05-07 : 19:31:47

DROP TABLE #ABC
CREATE TABLE #ABC (ID INT, Name VARCHAR (2))
INSERT INTO #ABC (ID, NAME)
VALUES (1,'01'),(1,'F5'),(1,'05'),(2,'08'),(2,'G4'),(3,'Y7'),(3,'18')

DROP TABLE #DEF
CREATE TABLE #DEF (ID INT, Name char(20))
INSERT INTO #DEF (ID, Name)
SELECT ID, SUBSTRING(REPLACE(CONVERT(VARCHAR(36), NEWID()) , '-', ''), 1, 2) AS Name
FROM #ABC

DROP TABLE #XYZ
CREATE TABLE #XYZ (ID INT, Name char(20))
INSERT INTO #XYZ (ID, Name)
SELECT #DEF.ID, #DEF.Name
FROM #DEF
LEFT JOIN #ABC ON #ABC.ID = #DEF.ID AND #ABC.Name = #DEF.Name
WHERE #ABC.ID IS NULL

SELECT * FROM #XYZ

I want the process to create a new 'name' which is already not taken by the same ID. in other words, same ID (say ID =1) can not have the same "Name" columns. This is just very few records but my real data is huge, I want system to generate only new Names with 2 characters in table#XYZ which are already not taken by the same ID in #ABC. Also, number of records inserted in #XYZ should be equal to #ABC Any help is appreciated. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 00:50:20

sounds like what you need a customer sequence based on ID field. You can still have same Name for different Ids right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ben53
Starting Member

2 Posts

Posted - 2013-05-08 : 00:52:35

Yes we can have same Names for different IDs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 01:13:26

the cant you create a custom generated sequence based on ID values?

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

zhulin

3 Posts

Posted - 2013-05-08 : 09:31:42

You need more of the statements in this matter, so that we can understand. Thank you for sharing. I hope you happy

__________________
zhulin
unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources