Copy whole row with a new primary key? - 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)

Copy whole row with a new primary key?

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2013-10-10 : 16:29:39

Hello I'm trying to copy a row in my table Items to a new row. The table has the primary key set to ItemsID. I've tried this:

INSERT INTO Items
SELECT *
FROM Items
WHERE (ItemsID = 668798)

I need it to do this MAX(ItemsID) + 1. As the new item id. How could I do that?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-10 : 16:32:17

Is ItemsID an identity column?

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

taunt
Posting Yak Master

128 Posts

Posted - 2013-10-10 : 17:10:53

Yep, and it can't have dupes or nulls.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-10 : 17:16:23

You will need to use SET IDENTITY_INSERT then:

SET IDENTITY_INSERT Items ON

INSERT INTO Items (ItemsID, Column2, Column3, Column4)
SELECT ItemsID+1, Column2, Column3, Column4
FROM Items
WHERE ItemsID = 668798

SET IDENTITY_INSERT Items OFF

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-10 : 17:16:32

If it is identity column, simply omit the ItemsID in your insert and select list.

INSERT INTO ITEMS (col1,col2,..coln)
SELECT col1, col2,... coln
from Items where ItemsId = 668798

Not that in my insert list and select list there is no ItemsId column. This will under most conditions, insert an id that is one plus the largest existing id unless you have deleted data from the table, or messed with the identity seed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-10 : 17:18:19

Oh I made an assumption in my post that I probably shouldn't have. I was assuming we were filling in a gap, rather than just inserting at the bottom. If you are inserting in the bottom, omit the IDENTITY_INSERT business from my post.

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

taunt
Posting Yak Master

128 Posts

Posted - 2013-10-11 : 11:49:59

Hello non of these seem to work. You got to remember I need to add a number to the max itemid in order to get it to work. Can I set the max(itemid) + 1 = newid and do it off that? I know I can do that in coldfusion when adding, but for some add reason I'm having issues doing it in a copy query.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-11 : 12:14:28

quote:
Originally posted by taunt

Hello non of these seem to work. You got to remember I need to add a number to the max itemid in order to get it to work. Can I set the max(itemid) + 1 = newid and do it off that? I know I can do that in coldfusion when adding, but for some add reason I'm having issues doing it in a copy query.

Would be useful for diagnosis if you can post what the error messages/behavior that you are seeing is. Tara's query should work - you just have to find what the max id is and add one to that, for example like this:

SET IDENTITY_INSERT Items ON
;WITH M(maxid) AS (SELECT COALESCE(MAX(ItemsId),0)+1 FROM Items)
INSERT INTO Items (ItemsID, Column2, Column3, Column4)
SELECT maxid, Column2, Column3, Column4
FROM Items CROSS JOIN M
WHERE ItemsID = 668798

SET IDENTITY_INSERT Items OFF

Edit: Fixed the issue that the code was not adding 1 to the MAX(ItemsId)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-11 : 12:53:54

Take Cold Fusion out of the picture until you get the query working. Run the query in Management Studio. Does it work there? If it does, then something is wrong on the Cold Fusion side, which is the likely culprit here since the code we posted is good.

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

taunt
Posting Yak Master

128 Posts

Posted - 2013-10-11 : 13:59:56

Well this is what I use to add something in CF:

<cfquery name="newItems" Datasource="#application.datasource#" dbtype="ODBC">
		SELECT MAX(ItemsID) + 1 AS newItemsID
		FROM Items
		</cfquery>
<cfset ItemsID = #newItems.newItemsID#> with 
<cfquery name="AddItems" datasource="#application.datasource#"> 
    INSERT INTO Items (ItemsID, ItemName, sku, description, CatID, upc)
    VALUES ('#ItemsID#', '#ItemName#', '#ItemNameFont#','#sku#', '#description#', #Form.CatID#, '#upc#') 
</cfquery>

That will add a new item, but copying I'm having issues with it doing the max(itemid) +1, for the new itemid.

quote:
Originally posted by tkizer

Take Cold Fusion out of the picture until you get the query working. Run the query in Management Studio. Does it work there? If it does, then something is wrong on the Cold Fusion side, which is the likely culprit here since the code we posted is good.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-11 : 14:23:08

There's no reason to copy it. Use the code James posted.

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

taunt
Posting Yak Master

128 Posts

Posted - 2013-10-11 : 15:33:25

Yep I got it working using a slight modded version of what James posted:
INSERT INTO Items
(ItemsID, ItemName)
SELECT (SELECT COALESCE (MAX(ItemsID), 0) + 1 AS maxid
FROM Items), ItemName
FROM Items AS Items_1
WHERE (ItemsID = 668794)

That worked like a charm, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources