Increment non identity column in INSERT query
-
Hi!
Could any one give me an advice please?
There is two tables
table A
Login [varchar](10)
Pass [varchar](10)
Name [varchar](10)
Age [int]
and table B
ID [int]
Name [varchar](10)
Age [int]
Column ID in table B is not identity (so, it has no auto-increment). It's important! I can't make this column identity =\
I need to move data (names and age) from table A to table B filling all columns and b.ID values must be unique.
So is there any opportunity to increment ID column while iserting???
I will be very appreciated for any advice
-
Yes, just create a temporary table with an identity column and insert from table A into that. Then find the max ID value from table B and insert into table B, adding that value to the values in your identity column. I take it you have a unique constraint on your ID column in table B? Otherwise you may have data integrity problems if someone else is inserting to table B at the same time as you.
By the way, I recommend chaning your Age column to date of birth and calculating age when you need to. That way your data won't become invalid as soon as someone gets another year older!
John
-
DECLARE @Target TABLE (RowID INT, Data VARCHAR(12))
INSERT @Target
SELECT 1, 'Peso' UNION ALL
SELECT 2, 'rm' UNION ALL
SELECT 3, 'Nosepicker'
DECLARE @Source TABLE (Data VARCHAR(12))
INSERT @Source
SELECT 'pologuy67' UNION ALL
SELECT 'SQLUSA'
-- Use simple correlated subquery
INSERT @Target
SELECT (SELECT MAX(RowID) + 1 FROM @Target),
Data
FROM @Source
-- Proof that simple correlated subquery will not work
SELECT *
FROM @Target
DELETE
FROM @Target
WHERE RowID > 3
-- Use double correlated subquery
INSERT @Target
SELECT (SELECT MAX(RowID) FROM @Target) + (SELECT COUNT(*) FROM @Source AS s2 WHERE s2.Data <= s1.Data),
s1.Data
FROM @Source AS s1
-- Proof that double correlated subquery will work
SELECT *
FROM @Target
N 56°04'39.16"
E 12°55'05.25" -
Oh, thanks a lot! temp table it's great
it didn't cross my mind.. and.. these tables (with Age as int) were only simpe example
Thanks again!
-
Peter,
Although the double correlated subquery will work, I'm also pretty sure it will kill batch performance because of the triangular join in it...
http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx#bm360151
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply