asked on

SQL statement

Hi,

How can I create a temp table that has a primary auto increment.

CREATE TABLE #TEMPPRJ
(
TPPrjID INT PRIMARY KEY Auto_increment,
TPProject char(24) NULL
)

it says incorrect syntax near Auto_increment.
 
there is more stuff to this question

Open in new window

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

For your reading pleasure:
http://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx

Best regards,
Kevin

chapmandew

no points please...just something else to read about besides what Kevin posted.

http://sqlservernation.com/blogs/tipweek/archive/2009/02/23/identity-columns.aspx

lulu50

ASKER

How can I append data to the temp table from a select statement say customer

lulu50

ASKER

select * from #TEMPPRJ

output is:

TPPrjID TPProject
01 58695
02 58659
03 59865

now I want to append to the temp table a column with data from
customer table

select customername from customer

Kevin Cross

Change this statement:
INSERT INTO #TEMPPRJ(TPProject)
SELECT 'Test'
UNION SELECT 'Another Test';

To something like this:
INSERT INTO #TEMPPRJ(TPProject)
SELECT DISTINCT ProjectColumnName
FROM Customer

Kevin Cross

If you mean, you want to join on project to get the customer name then do something like this:

SELECT t.*, c.customername
FROM #TEMPPRJ t
INNER JOIN Customer c ON c.TPProject = t.TPProject

OR something to that effect. If you meant to insert new rows of TPProject values based on customername values in your customer table, see my original suggestion using INSERT INTO.

lulu50

ASKER

Excellent Thank you