Help creating an indexed table

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help creating an indexed table

Author

Topic

jkirk210
Starting Member

4 Posts

Posted - 2012-04-04 : 13:29:24

Hi all, newbie at SQL here, I researched this a good bit, but found no help elswhere so here goes;
SQL 2008
I am trying to create a table or I should say pulling data from one table into a new table but having an indexed column. I can do a make table with Access, but as best as I can tell cant do a make table with an indexed column. I was hoping SQL can do this. I know I can CREATE INDEX, and I can also CREATE TABLE, but cant figure out how to CREATE TABLE with my inv_no column with an index.
Here's my table oehdrhst
here are the 3 columns I want to pull
ord_no
inv_no (need indexed)or unique index
inv_dt

Any help would be greatly appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 13:40:40

I have to assume data types since you didn't tell us

CREATE TABLE oehdrhst (ord_no int, inv_no int, inv_dt date, CONSTRAINT PK_oehdrhst PRIMARY KEY (inv_no))
GO

INSERT INTO oehdrhst (ord_no, inv_no, inv_dt)
SELECT ord_no, inv_no, inv_dt FROM yourTable
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jkirk210
Starting Member

4 Posts

Posted - 2012-04-04 : 14:46:18

Thanks for the quick response, I ran this which is in fact creating the table and making the index as needed, I am getting an error which as best as I can tell means that I somehow have duplicate invoice numbers in my current column inv_no.

CREATE TABLE oehdrhsttest (ord_no char(8), inv_no char(8), inv_dt dateTIME, CONSTRAINT PK_oehdrhsttest PRIMARY KEY (inv_no))
GO

INSERT INTO oehdrhsttest (ord_no, inv_no, inv_dt)
SELECT ord_no, inv_no, inv_dt FROM dbo.oehdrhst_sql
GO

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_oehdrhsttest'. Cannot insert duplicate key in object 'dbo.oehdrhsttest'.
The statement has been terminated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-04 : 14:58:11

Brett showed how to create a primary key, which is a UNIQUE index.

If that's not what you want, drop it, and just create an index with CREATE INDEX syntax.

There is no syntax to create an index in a create table statement, other than what Brett showed above.

EDIT: by the way, you're right -- you seem to have duplicates in the source.

jkirk210
Starting Member

4 Posts

Posted - 2012-04-04 : 15:32:03

Thanks Brett, Thanks russell,

This was a huge help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 15:58:03

He mea iki

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 16:00:12

Wait..are you suppose to have duplicates?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jkirk210
Starting Member

4 Posts

Posted - 2012-04-04 : 16:15:45

No I dont want duplicates, but I gave a bad example using the invoice number from our dbase, there are likely duplicate invoice numbers in this table. I reran your code in the following and acheived the results I need.

CREATE TABLE PARTS (item_no char(30), item_desc_1 char(30), CONSTRAINT PK_PARTS PRIMARY KEY (item_no))
GO

INSERT INTO PARTS (item_no, item_desc_1)
SELECT item_no, item_desc_1 FROM dbo.imitmidx_sql
GO

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-04 : 16:24:29

Mahalo

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources