SELECT INTO query

Question

I have to write an SELECT INTO T-SQL script for a table which has columns acc_number, history_number and note.

How do i facilitate an incremental value of history_number for each record being inserted via SELECT INTO.

Note, that the value for history_number comes off as a different value for each account from a different table.

The table already exists, i am populating it with values from a different database. It is not an identity column. — Abhi, Jul 29 '14 at 22:41
You need to increment based on the ID, then restart the increment for each new ID? Use `Rank()`. If that doesnt describe what you are trying to do add some sample input/output please. — Daniel E., Jul 29 '14 at 22:54
I need to increment based on the account number, for example : If there are 2 entries for the same account then the second entry will have +1 value for the history_number. — Abhi, Jul 29 '14 at 22:56
It might be a good idea to illustrate your problem with an example (what the source looks like, what the corresponding result should be). — Andriy M, Jul 30 '14 at 05:34

score 0 · Accepted Answer · answered Jul 29 '14 at 22:09

0

SELECT history_number = IDENTITY(INT,1,1),
    ... etc...
INTO NewTable
FROM ExistingTable
WHERE ...

You could use ROW_NUMBER instead of identity i.e. ROW_NUMBER() OVER (ORDER BY )

answered Jul 29 '14 at 22:09

Jason Campbell

146
1
10

Hi Jason, it is not an identity column. It's an integer value coming off from another table and the values picked up for increment are different for each account.[there are several records for each account being picked up so i need to pick up the history_number value for an account and then keep incrementing it for every record it has.] – Abhi Jul 29 '14 at 22:44

score 0 · Answer 2 · edited May 23 '17 at 10:24

SELECT acc_number
    ,o.historynumber
    ,note
    ,o.historynumber+DENSE_RANK() OVER (Partition By acc_number ORDER BY Note) AS NewHistoryNumber 
                                                              --Or some other order by probably a timestamp...
FROM Table t
INNER JOIN OtherTable o
ON ....

Working Fiddle

The will give you an incremented count starting from history number for each accnum. I suggest you use a better order by in the rank but there was not enough info in the question.

This answer to this question may help you as well Question

score 0 · Answer 3 · answered Jul 30 '14 at 05:21

Suppose your SELECT statement is like this

SELECT  acc_number,
        history_number,
        note
FROM    [Table]

Try this Query as below.

SELECT  ROW_NUMBER() OVER (ORDER BY acc_number) ID,
        acc_number,
        history_number,
        note    
INTO    [NewTable]
FROM    [Table]

SELECT INTO query

3 Answers3