Creating table with a primary key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating table with a primary key

Author

Topic

matpj
Starting Member

8 Posts

Posted - 2006-06-29 : 03:29:41

Hi,

I need to create a new table in our database.
This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not.
either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement.
I have tried searching but cannot find the answer.

many thanks,
Matt

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-06-29 : 03:38:46

[code]CREATE TABLE [dbo].[table1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Column1] [varchar](50) NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([ID] ASC)
)[/code]--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-29 : 03:41:52

Create Table Abc
(
Column_Name data_Type Primary Key,
Column2....,
Column3... etc.
)You just have to add the keyword "Primary Key" in front of the column definition as shown above.

Harsh Athalye
India.
"Nothing is Impossible"

matpj
Starting Member

8 Posts

Posted - 2006-06-29 : 03:46:54

thanks guys.

now for the other question,

is it possible to populate more than one row with an INSERT Statement?

thanks again,
Matt

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-06-29 : 03:50:46

The only way I can think of right now is to use a select with the insert:

INSERT INTO table1 (Column1, Column2)
SELECT Column1, Column2
FROM table2
WHERE ...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-29 : 03:53:48

You can do that by using INSERT..SELECT construct as shown below:

If you have SomeTable already created:
--------------------------------------

Insert into SomeTable
(col1, col2, col3,...coln)
Select col1, col2, col3,...coln from OtherTable

If you don't have SomeTable already created:
--------------------------------------------

Select Col1, col2, col3,...coln
Into SomeTable
from OtherTable

Harsh Athalye
India.
"Nothing is Impossible"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-29 : 06:12:15

And if you're not selecting from another table, you can use the union operator...

declare @t table (id int)
insert @t
          select 343
union all select 232
union all select 201

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-29 : 07:42:02

quote:
Originally posted by matpj

is it possible to populate more than one row with an INSERT Statement?

INSERTs always populate by the row. Look at Ryan's posting.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources