Primary key generating

[mutemode]

Help again please,
I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column. Much appreciated.

[ZeldorBlat]

mu******@gmail.com wrote:

Help again please,
I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column. Much appreciated.

Then create an auto-incrementing identity column in the destination
table and insert away.

[mutemode]

ZeldorBlat wrote:

mu******@gmail.com wrote:

Help again please,
I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column. Much appreciated.

Then create an auto-incrementing identity column in the destination
table and insert away.

Sorry, both tables don't have an identity column.

[ZeldorBlat]

[e-mail] wrote:

ZeldorBlat wrote:

mu******@gmail.com wrote:

Help again please,
I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column. Much appreciated.

Then create an auto-incrementing identity column in the destination
table and insert away.

Sorry, both tables don't have an identity column.

Allow me to reiterate my previous response: create an auto-incrementing
identity column in the destination table, then do an insert.

[mutemode]

OK maybe I didn't explain it well enough
I have
TABLE1 TABLE2
First Last ID
First Last
John Smith 1
Lou Price
George Bluth 2
Henry Joe

What I WANT is

TABLE2
ID First Last
1 Lou Price
2 Henry Joe
3 John Smith
4 George Bluth
ZeldorBlat wrote:

[e-mail] wrote:

ZeldorBlat wrote:

mu******@gmail.com wrote:
Help again please,
I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column. Much appreciated.
>
Then create an auto-incrementing identity column in the destination
table and insert away.

Sorry, both tables don't have an identity column.

Allow me to reiterate my previous response: create an auto-incrementing
identity column in the destination table, then do an insert.

[Alexander Kuznetsov]

SET NOCOUNT ON
go

CREATE TABLE Target(TargetID INT PRIMARY KEY, someData CHAR(1))
INSERT Target VALUES(1, 'A')
INSERT Target VALUES(2, 'B')
go
create table staging(someData CHAR(1))
INSERT staging VALUES('C')
INSERT staging VALUES('D')
go
SELECT IDENTITY(INT, 1,1) AS TargetID, someData
INTO #t FROM staging

INSERT Target
SELECT TargetID + (SELECT COALESCE(MAX(TargetID), 0) FROM Target),
someData
FROM #t

go
SELECT * FROM Target
go

TargetID someData
----------- --------
1 A
2 B
3 C
4 D
DROP TABLE Target
DROP TABLE Staging
DROP TABLE #t

[mutemode]

Thanks! Worked great. Sorry for not understanding it earlier Zeldor :)
Alexander Kuznetsov wrote:

SET NOCOUNT ON
go

CREATE TABLE Target(TargetID INT PRIMARY KEY, someData CHAR(1))
INSERT Target VALUES(1, 'A')
INSERT Target VALUES(2, 'B')
go
create table staging(someData CHAR(1))
INSERT staging VALUES('C')
INSERT staging VALUES('D')
go
SELECT IDENTITY(INT, 1,1) AS TargetID, someData
INTO #t FROM staging

INSERT Target
SELECT TargetID + (SELECT COALESCE(MAX(TargetID), 0) FROM Target),
someData
FROM #t

go
SELECT * FROM Target
go

TargetID someData
----------- --------
1 A
2 B
3 C
4 D
DROP TABLE Target
DROP TABLE Staging
DROP TABLE #t

[Erland Sommarskog]

(mu******@gmail.com) writes:

I need to insert rows into a table from another table. The tables are
identical column wise except the table im inserting from does not have
a primary key value. On insert I need to generate a primary key that
is consecutive based on the table im inserting into. Also the table im
inserting into does not have a identity column. Much appreciated.

On SQL 2005:

INSERT target (ID, col2, col2, ....)
SELECT row_number OVER (ORDER BY <whatever you want to order by)>,
col1, col2, ...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx