Add mutiplt field with auto generate number? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Add mutiplt field with auto generate number?

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2014-07-28 : 17:24:41

Hello, I'm trying to add mutiplt columns from a database (AECCVFD) into another table that has a field that's the primary key (LabelID).
Here's what I'm trying:


INSERT INTO Label
            (LabelID, LabelAbbr, LabelCode, LabelCodeAEC, LabelName, SuperDNumber, VendorID)
SELECT (SELECT MAX(LabelID) + 1 AS Expr1
              FROM   Label) AS Expr1, LabelAbbreviation, LabelAbbreviation AS Expr2, LabelAbbreviation AS Expr3, LabelName,
                (SELECT MAX(LabelID) + 1 AS Expr1
                FROM  Label) AS Expr4, '242' AS Expr5
FROM  AECCVFD
WHERE (LabelAbbreviation = 'FAT') or
(LabelAbbreviation = 'GTMR')

What happens is I get an error saying can not insert duplicate keys in label database. I can do it fine when adding one, but multiples screws up sql. What would I change to make it work? I need 3 fields to have the same info from the LabelAbbreviation in AECCVFD. The SuperDNumber = LabelID (number that was auto generated). Hopes this makes sense, thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 17:42:55

I think a cross join would work.

INSERT INTO Label
(LabelID, LabelAbbr, LabelCode, LabelCodeAEC, LabelName, SuperDNumber, VendorID)
SELECT LabelID+1, LabelAbbreviation, LabelAbbreviation AS Expr2, LabelAbbreviation AS Expr3, LabelName,
LabelID+1, '242' AS Expr5
FROM AECCVFD
CROSS JOIN Label
WHERE (LabelAbbreviation = 'FAT') or
(LabelAbbreviation = 'GTMR')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

taunt
Posting Yak Master

128 Posts

Posted - 2014-07-28 : 17:45:36

Nope got the same error.

quote:
Originally posted by tkizer

I think a cross join would work.

INSERT INTO Label
(LabelID, LabelAbbr, LabelCode, LabelCodeAEC, LabelName, SuperDNumber, VendorID)
SELECT LabelID+1, LabelAbbreviation, LabelAbbreviation AS Expr2, LabelAbbreviation AS Expr3, LabelName,
LabelID+1, '242' AS Expr5
FROM AECCVFD
CROSS JOIN Label
WHERE (LabelAbbreviation = 'FAT') or
(LabelAbbreviation = 'GTMR')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 17:47:08

I'm not understanding the issue then. Please post sample data that clearly shows the issue and what you want to happen.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 17:47:20

Also this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

taunt
Posting Yak Master

128 Posts

Posted - 2014-07-28 : 18:24:55

OK I figured out the error. It was from when it see's a items with multiple listings. Thats what is causeing it to error out. What can I put in the original query to ignore items with multiple results and make it only add one?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-28 : 18:30:55

DISTINCT or GROUP BY, but really this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

taunt
Posting Yak Master

128 Posts

Posted - 2014-07-29 : 14:45:57

Yep I'm still having issues with adding multiple things. It doesn't know what to do for the second item. It keeps giving it the same number as the first thing entered. For example:
INSERT INTO Label
(LabelID, LabelAbbr, LabelCode, LabelCodeAEC, LabelName, SuperDNumber, VendorID)
SELECT (SELECT MAX(LabelID) + 1 AS Expr1
FROM Label) AS Expr1, LabelAbbreviation, LabelAbbreviation AS Expr2, LabelAbbreviation AS Expr3, LabelName,
(SELECT MAX(LabelID) + 1 AS Expr1
FROM Label) AS Expr4, '242' AS Expr5
FROM AECCVFD
WHERE (LabelAbbreviation = 'FAT') or
(LabelAbbreviation = 'GTMR')

FAT would be entered as LabelID 55, but when it adds the second one GTMR it would also have the LabelID 55. Sense the LabelID is a primary key it would allow it to add it. This is driving me crazy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-29 : 16:08:40

We can't help until you take the time to describe the issue and provide the necessary info for us to work on your issue on our own machines. Please see this for details: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-07-29 : 19:48:38

[code]

INSERT INTO Label
(LabelID, LabelAbbr, LabelCode, LabelCodeAEC, LabelName, SuperDNumber, VendorID)
SELECT
LabelID_Max + aec.row_num, aec.LabelAbbreviation, aec.LabelAbbreviation AS Expr2, aec.LabelAbbreviation AS Expr3,
aec.LabelName, LabelID_Max + aec.row_num, aec.Expr5
FROM (
SELECT LabelAbbreviation, LabelName, '242' AS Expr5,
ROW_NUMBER() OVER(ORDER BY LabelAbbreviation, LabelName) AS row_num
FROM AECCVFD
WHERE (LabelAbbreviation = 'FAT') or
(LabelAbbreviation = 'GTMR')
) AS aec
CROSS JOIN (
--must lock the row so that no one else uses the same
--starting LabelID when INSERTing rows
SELECT MAX(LabelID) AS LabelID_Max
FROM Label WITH (ROWLOCK, XLOCK)
) AS cj1

[/code]

Edit: SQL will only evaluate the expression "MAX(LabelID) + 1" in your previous SQL one time, so all new rows will get the same value. Instead, you need to take the current high LabelID and add a counter to each new row to add to the LabelID, insuring that all new rows have a unique LabelID.

tm
Posting Yak Master

160 Posts

Posted - 2014-07-30 : 08:33:55

Here is something similar to ScottPletcher solution ..

declare @Label table (labelID int, LabelAbbr varchar(20))

insert into @Label (labelID)
select 1
union all
select 2
union all
select 3

declare @AECCVFD table (LabelName varchar(20), LabelAbbr varchar(20))

insert into @AECCVFD (LabelName, LabelAbbr)
select 'L1', 'FAT'
union all
select 'L1', 'GMTR'

select * from @AECCVFD

select ROW_NUMBER() OVER (order by LabelAbbr)
, LabelAbbr
from @AECCVFD

insert into @Label (LabelID, LabelAbbr)
SELECT (SELECT MAX(LabelID) AS Expr1 FROM @Label) +
ROW_NUMBER() OVER (order by LabelAbbr)
AS Expr1
, LabelAbbr
FROM @AECCVFD
WHERE ((LabelAbbr = 'FAT') or
(LabelAbbr = 'GMTR'))

select * from @Label

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources