sql how to insert into a column like it was an identity column

Question

I have a table with three columns

KeyID   | nvarchar(10)  
ColumnA | nvarchar(max)  
ColumnB | Nvarchar(max)

What i'm trying to do is a select insert, however with keyID i need to treat it like an indentity column.

For example if I had 3 rows I wanted to insert it would be :

1001 | Apple     | Pear  
1002 | Pear      | Mango  
1003 | Pineapple | Pine

But i'm trying to do this with an insert select, for example:

insert into myTable(KeyId,ColumnA,ColumnB) select 'x',OrigColA,OrigColB from myTableB

obviously 'x' is where i'm having the issue.

Thanks in advance!

This is no KeyId located in myTableB

Answer 1

This is a bit ugly because of the varchar, but you can try:

;WITH x AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY OrigColA, OrigColB) AS n, OrigColA, OrigColB
  FROM myTableB
)
INSERT INTO myTable(KeyId, ColumnA, ColumnB)
SELECT (SELECT CAST(CAST(MAX(KeyId) AS INT) + n AS NVARCHAR(10)) FROM myTable),
    OrigColA, OrigColB
FROM x;

This of course assumes there is already a row in myTable to get the current maximum value from.

SQL Fiddle example

If that's not always true, wrap MAX(KeyId) with a COALESCE and the constant of one less than your starting default. For example:

;WITH x AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY OrigColA, OrigColB) AS n, OrigColA, OrigColB
  FROM myTableB
)
INSERT INTO myTable(KeyId, ColumnA, ColumnB)
SELECT (SELECT CAST(CAST(COALESCE(MAX(KeyId),1000) AS INT) + n AS NVARCHAR(10)) FROM myTable),
    OrigColA, OrigColB
FROM x;

Or if you know there isn't a row and you just want to start at a constant, just do:

INSERT INTO myTable(KeyId, ColumnA, ColumnB)
SELECT ROW_NUMBER() OVER(ORDER BY OrigColA, OrigColB) + 1000, OrigColA, OrigColB
FROM myTableB

Answer 2

you need a db with sequences. heres a workaround for sql server

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