Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:
> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do with
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
> >I have table with several million rows, which I would like to move to
> > partition schema.
> >
> > I've found this option is present in ALTER TABLE command. However, this
> > command
> >
> > alter table TaleName move to ps_Schema ( PartitionColumnName)
> >
> > resulted in error:
> >
> > Msg 102, Level 15, State 1, Line 1
> > Incorrect syntax near 'move'.
> >
> > I assume the syntax is wrong, but how to accomplish this task ? What is
> > the
> > correct syntax?
> >
> > Thank you very much.
> >
>
>

Move table to partition schema.

I have table with several million rows, which I would like to move to
partition schema.
I've found this option is present in ALTER TABLE command. However, this
command
alter table TaleName move to ps_Schema ( PartitionColumnName)
resulted in error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
I assume the syntax is wrong, but how to accomplish this task ? What is the
correct syntax?
Thank you very much.I assume you are using SQL Server 2005.
Please read the full definition of ALTER TABLE again.
The MOVE can only be used as part of dropping a clustered index, so it is
the clustered index that is moving. The clustered index needs to have been
created as part of a primary key or unique constraint.
Make sure you already have the partition scheme defined.
The syntax would be something like this (untested):
ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
WITH MOVE TO ps_Schema ( PartitionColumnName)
(I am also assuming you are aware a partition scheme has nothing to do with
schema, so that your name ps_schema is just a typo.)
--
HTH
Kalen Delaney, SQL Server MVP
"BP" <BP@.discussions.microsoft.com> wrote in message
news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>I have table with several million rows, which I would like to move to
> partition schema.
> I've found this option is present in ALTER TABLE command. However, this
> command
> alter table TaleName move to ps_Schema ( PartitionColumnName)
> resulted in error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'move'.
> I assume the syntax is wrong, but how to accomplish this task ? What is
> the
> correct syntax?
> Thank you very much.
>|||Kalen,
Thank you very much. I am admired with your articles and books.
I am waiting to buy your new book.
The correct syntax is:
ALTER TABLE dbo.Test DROP CONSTRAINT pk_Test
WITH (MOVE TO ps_OrderDate (OrigOrderDT));
Thank you again.
"Kalen Delaney" wrote:

> I assume you are using SQL Server 2005.
> Please read the full definition of ALTER TABLE again.
> The MOVE can only be used as part of dropping a clustered index, so it is
> the clustered index that is moving. The clustered index needs to have been
> created as part of a primary key or unique constraint.
> Make sure you already have the partition scheme defined.
> The syntax would be something like this (untested):
> ALTER TABLE table_name DROP CONSTRAINT clustered_index_constraint_name
> WITH MOVE TO ps_Schema ( PartitionColumnName)
> (I am also assuming you are aware a partition scheme has nothing to do wit
h
> schema, so that your name ps_schema is just a typo.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "BP" <BP@.discussions.microsoft.com> wrote in message
> news:5791C6D0-7BFA-4B56-B6CB-240783F88F5D@.microsoft.com...
>
>

Move next and back through rows using datareader

I have multiple dropdownlists each one filled with values from a specific column in the table. Also I have multiple textboxes corresponding to dropdownlists. For example, when I select an item from dropdownlistA, all the textboxes are filled with the first row values that contains that selected item and gives the number of rows containing this value……. In addition, I have 2 buttons one is Move Forward Button and the other is Move Previous…I am using a Record Set and don't know how to move next and back throughout the selected rows…could you help me please? I am using a vb codebehind… Thanks

A datareader (a recordset should not be used in ASP.NET) is strictly forward only. But, even if it wasn't, the real issue here is that you need to either store the results of your query in some kind of state, between requests, or requery the database to get the previous or the next result.

Please show us a bit of your code, and we'll likely be able to devise a good method.

|||

Thanks for your response,

I have 6 dropdownlists like the following,

PrivateSub fillDropDownList3()

Dim sqlStringAsString = "select * from ItemName"

Dim cmdAsNew SqlCommand(sqlString, conn)

cmd.CommandType = CommandType.Text

Dim drAs SqlDataReader

conn.Open()

dr = cmd.ExecuteReader

While dr.Read

Me.DropDownList3.Items.Add(dr(0))

EndWhile

dr.Close()

conn.Close()

PrivateSub DropDownList3_SelectedIndexChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles DropDownList3.SelectedIndexChanged

Dim countAsIntegerDim sqlStringAsString = " select * from ReceiptItems where ItemName=@.ItemName"Dim cmdAsNew SqlCommand(sqlString, conn)

cmd.CommandType = CommandType.Text

cmd.Parameters.Add("@.ItemName",Me.DropDownList3.SelectedItem.Text)

Dim drAs SqlDataReader

conn.Open()

dr = cmd.ExecuteReader

While dr.Read()

count = count + 1

Me.TextBox3.Text = dr("Name")

Me.TextBox4.Text = dr("Dept")

Me.TextBox5.Text = dr("ItemName")

Me.TextBox6.Text = dr("ItemDescription")

Me.TextBox7.Text = dr("ItemSN")

Me.TextBox8.Text = dr("Date")

EndWhile

dr.Close()

conn.Close()

Me.TextBox2.Text = count

EndSub

but in the textbox corresponding to it, it just gives the first result. What I need is to know how to move to the next record, or get back to the previous one by clicking a button...

Thanks

|||Hi diana_j86,
I've reviewed your code and it seems i can somewhat figure out what you want.However, I need to clarify frist that: You query string

select * from receiptitems whereitemname=@.ItemName

would return more than one values. If i'm wrong, please ignore my suggestion and inform me.

As Norrkoping has said, sqldatareader is strictly forward only so you can NOT use it if you want to get a previous record. I suggest you use dataset(datatable) with sqldataadapter.
You code should look like this: (Sorry, i'm not familiar with vb.net , so c# instead)

page-scope variables:

DataTable tbl = new DataTable();
int count = 0; //the count of records
int current = 0; // current record showed in your text boxes

dropdownlistbox select change event handle: 

 SqlDataAdapter adpter =new SqlDataAdapter(); adpter.SelectCommand =new SqlCommand("select * from receiptItems where itemsname=@.ItemName"); adpter.SelectCommand.Parameters.Add("@.ItemName",dropdownlist3.SelectdItem.Text); adpter.Fill(tbl); count = tbl.Rows.Count;

 current=0; // ALWAYS SHOW THE FIRST ONE AFTER SLECT CHANGED textbox2.text=tbl[0][0];

 .................

Next_Button_Clicked Event handler: (Previous_Button_Clicked Event likewise)

textbox2.text=rows[current+1][0];

textbox3.text=rows[current+1][1];

....

Hope my suggestion helps

|||

Thanks, Bo Chen – MSFT.

Move large table from DB to DB

I have a table of approx 1/2 million rows.

On a nightly basis, this table gets rebuilt in a temporary database. Once the table has been built and scrubbed, i need to move it into our webservers db.

I'd like to do this with minimal interuption to the website.

Possible techniques:

1) I could set up a DTS package to copy the table object overwriting the destination table

2) I could export to a flat file and then bulk import into the live table (after truncating it)

3) I could run a process to update smaller chunks of data at a time running delete queries and insert queries.

Anybody have a thought on the best way to do this so that the web users would be virtually unaware that anything was happening ?Is the webserver's db.. like at a remote location?

I'd go with loading that temp db to another temp db on the webserver, and reverse engineer it.|||The temp DB is on the same SQL server as the webservers DB.

so the questions remains....

What's the best way to move a table from DB to DB with minimal interuption to the user?|||I use DTS for almost the same thing, but my table is < 1gig. I dont notice any slowdown, but I probably only have 5 current users at any instance.|||are you object copying or bulk inserting ?

Move data out of a table

I need to move certain rows out of a table into a temporary table then pull them back in.

These tables are designed by a third party, and have triggers which unfortunately only work on new records.

So I need to create a new temp table with the same sturcture as the first one, move the selected records into the new table, then pull them back in...

any ideas
thanks guys

dbforums has been a great helphow about

select * into #Tmp from <your 3rd party table> where <youer where clause>.

This will produce a temp table close enough if not exactly like the original. There are a few things that could prevent an exact duplicate structure but I would not be conserned.

If you need to create an empty table first then select * into #Tmp from <your 3rd party table> where 1=2.