• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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

0
lulu50
Asked:
lulu50
  • 4
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Try like this:
CREATE TABLE #TEMPPRJ
(
      TPPrjID INT PRIMARY KEY IDENTITY(1,1),
      TPProject char(24) NULL
);
 
INSERT INTO #TEMPPRJ(TPProject)
SELECT 'Test'
UNION SELECT 'Another Test';
 
SELECT * FROM #TEMPPRJ;
 
DROP TABLE #TEMPPRJ;

Open in new window

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

Best regards,
Kevin
0
 
chapmandewCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
lulu50Author Commented:
How can I append data to the temp table from a select statement say customer
0
 
lulu50Author Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
lulu50Author Commented:
Excellent Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now