Index help - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Index help

Author

Topic

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2006-09-19 : 10:33:55

Hi all,

I need to copy my table from one db to other and my table has indexes i think. I have two questions. How do I copy one my table and other is :

CREATE TABLE [az_Ques] (
[QuestD] [int] IDENTITY (1, 1) NOT NULL ,
[usd_FormID] [int] NOT NULL ,
[usd_ItemID] [int] NOT NULL ,
[usd_MatCatID] [int] NULL ,
[usd_MatetID] [int] NULL ,
[usd_IType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InActiveDate] [datetime] NULL ,
CONSTRAINT [PKey_az_Ques] PRIMARY KEY CLUSTERED
(
[QuesID]
) ON [PRIMARY] ,
CONSTRAINT [Index_Quest_Unique] UNIQUE NONCLUSTERED
(
[usd_FormID],
[usd_ItemID],
[usd_MatCatID],
[usd_MatetID]
) ON [PRIMARY]
) ON [PRIMARY]

In my above staement, I din't understand second constraint. Is that way of adding index?
CONSTRAINT [Index_Ques_Unique] UNIQUE NONCLUSTERED
(
[usd_FormID],
[usd_ItemID],
[usd_MatCatID],
[usd_MatetID]
) ON [PRIMARY]
Above is the part , I didn't understand. Plz help me out.

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-19 : 10:37:54

have a look at alter table

alter table mytable add constraint ....

There are many ways of copying a table

dmo - see
http://www.nigelrivett.net/DMO/SQL-DMOTransfer.html
You could probably copy the constraints with it

dts
select into
...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2006-09-19 : 11:40:08

Thanks for your help. I tried it with select into statement but it doesn't copy index that I have on table. Do you know any other way of copy table(both structure and data).

Thanks!

Kristen
Test

22859 Posts

Posted - 2006-09-19 : 13:21:50

This question is part of ~~http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72207~~
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72213

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-19 : 14:31:32

quote:
Originally posted by Kristen

This question is part of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72207

, are you hinting a recursive function K?

Since you can do SELECT INTO the databases must be on the same server, or linked.

Script out the table with indexes and constraints,
then do an INSERT into the table (from on db to the other).

INSERT db1.dbo.tbl (...) SELECT ... FROM db2.dbo.tbl

You may want to apply indexes, and possibly constraints after the INSERT.

rockmoose

Kristen
Test

22859 Posts

Posted - 2006-09-19 : 15:06:07

"are you hinting a recursive function K?"

Spider food!

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2006-09-19 : 15:15:52

Thanks you all for your help.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-19 : 15:28:33