Primary key generating

[mutemode@gmail.com]

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]

Re: Primary key generating


mutemode@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@gmail.com]

Re: Primary key generating


ZeldorBlat wrote:

mutemode@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]

Re: Primary key generating


mutem...@gmail. com wrote:

ZeldorBlat wrote:

mutemode@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@gmail.com]

Re: Primary key generating

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:

mutem...@gmail. com wrote:

ZeldorBlat wrote:

mutemode@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]

Re: Primary key generating

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(someDat a 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(Ta rgetID), 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@gmail.com]

Re: Primary key generating

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(someDat a 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(Ta rgetID), 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]

Re: Primary key generating

(mutemode@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx