I am using mssql db and access it via SQL server management studio.
is it possible to move a table from 1 db on my server to another db on my
server?
if it can't be done through that program, can it be done with script?
like select * from tablea
and close the connection, then open the connection of the second db..
and select it into there??
Jeff wrote:
> I am using mssql db and access it via SQL server management studio.
>
> is it possible to move a table from 1 db on my server to another db
> on my server?
>
> if it can't be done through that program, can it be done with script?
>
> like select * from tablea
>
> and close the connection, then open the connection of the second db..
> and select it into there??
It depends on what you need to move: just the data? or do you need the
resulting table to have all the metadata as well? Indexes, constraints,
relationships, etc.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
I need to move the enitre table, Indexes, constraints,
relationships, everything. worse case senario, i could build the tables, and
just move the data, but would rather do it all at once.
"Bob Barrows [MVP]"
news:eKaM3$3fGHA.3364@TK2MSFTNGP05.phx.gbl...
> Jeff wrote:
>> I am using mssql db and access it via SQL server management studio.
>>
>> is it possible to move a table from 1 db on my server to another db
>> on my server?
>>
>> if it can't be done through that program, can it be done with script?
>>
>> like select * from tablea
>>
>> and close the connection, then open the connection of the second db..
>> and select it into there??
>
> It depends on what you need to move: just the data? or do you need the
> resulting table to have all the metadata as well? Indexes, constraints,
> relationships, etc.
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
Jeff wrote:
> I need to move the enitre table, Indexes, constraints,
> relationships, everything. worse case senario, i could build the
> tables, and just move the data, but would rather do it all at once.
There's no way to do this in one fell swoop, short of restoring a backup.
>
> "Bob Barrows [MVP]"
> news:eKaM3$3fGHA.3364@TK2MSFTNGP05.phx.gbl...
>> Jeff wrote:
>>> I am using mssql db and access it via SQL server management studio.
>>>
>>> is it possible to move a table from 1 db on my server to another db
>>> on my server?
>>>
>>> if it can't be done through that program, can it be done with
>>> script? like select * from tablea
>>>
>>> and close the connection, then open the connection of the second
>>> db.. and select it into there??
>>
>> It depends on what you need to move: just the data? or do you need
>> the resulting table to have all the metadata as well? Indexes,
>> constraints, relationships, etc.
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
ok. so if i create the tables in the target db, i should be able to move the
data between diff db's??
"Bob Barrows [MVP]"
news:%23rpQmX5fGHA.4900@TK2MSFTNGP02.phx.gbl...
> Jeff wrote:
>> I need to move the enitre table, Indexes, constraints,
>> relationships, everything. worse case senario, i could build the
>> tables, and just move the data, but would rather do it all at once.
>
> There's no way to do this in one fell swoop, short of restoring a backup.
ok. so if i create the tables in the target db, i should be able to move the
data between diff db's??
>
>>
>> "Bob Barrows [MVP]"
>> news:eKaM3$3fGHA.3364@TK2MSFTNGP05.phx.gbl...
>>> Jeff wrote:
>>>> I am using mssql db and access it via SQL server management studio.
>>>>
>>>> is it possible to move a table from 1 db on my server to another db
>>>> on my server?
>>>>
>>>> if it can't be done through that program, can it be done with
>>>> script? like select * from tablea
>>>>
>>>> and close the connection, then open the connection of the second
>>>> db.. and select it into there??
>>>
>>> It depends on what you need to move: just the data? or do you need
>>> the resulting table to have all the metadata as well? Indexes,
>>> constraints, relationships, etc.
>>>
>>> --
>>> Microsoft MVP - ASP/ASP.NET
>>> Please reply to the newsgroup. This email account is my spam trap so
>>> I don't check it very often. If you must reply off-line, then remove
>>> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
Oh, yes. No problem.
INSERT INTO newdb..table SELECT * FROM olddb..table
Why do you need this to happen via asp? This does not seem like an activity
that should happen often enough to need to to be triggered by an asp page.
Why wouldn't you use a DTS package to move the table and data?
Oh, you can use the wizard to create a DTS package which can be triggered to
run via ASP:
http://www.sqldts.com/default.aspx?207,1
But again, unless you are creating an online substitute for Enterprise
Manager, I would be hesitant about tying up IIS resources with this
activity.
Jeff wrote:
> ok. so if i create the tables in the target db, i should be able to
> move the data between diff db's??
>
>
> "Bob Barrows [MVP]"
> news:%23rpQmX5fGHA.4900@TK2MSFTNGP02.phx.gbl...
>> Jeff wrote:
>>> I need to move the enitre table, Indexes, constraints,
>>> relationships, everything. worse case senario, i could build the
>>> tables, and just move the data, but would rather do it all at once.
>>
>> There's no way to do this in one fell swoop, short of restoring a
>> backup.
>
> ok. so if i create the tables in the target db, i should be able to
> move the data between diff db's??
>
>
>
>
>>
>>>
>>> "Bob Barrows [MVP]"
>>> news:eKaM3$3fGHA.3364@TK2MSFTNGP05.phx.gbl...
>>>> Jeff wrote:
>>>>> I am using mssql db and access it via SQL server management
>>>>> studio. is it possible to move a table from 1 db on my server to
>>>>> another
>>>>> db on my server?
>>>>>
>>>>> if it can't be done through that program, can it be done with
>>>>> script? like select * from tablea
>>>>>
>>>>> and close the connection, then open the connection of the second
>>>>> db.. and select it into there??
>>>>
>>>> It depends on what you need to move: just the data? or do you need
>>>> the resulting table to have all the metadata as well? Indexes,
>>>> constraints, relationships, etc.
>>>>
>>>> --
>>>> Microsoft MVP - ASP/ASP.NET
>>>> Please reply to the newsgroup. This email account is my spam trap
>>>> so I don't check it very often. If you must reply off-line, then
>>>> remove the "NO SPAM"
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
thanks bob,
this will only happen once. i onle need to move the tables from one db to
another one time.
I am condensing my webs, and realized that stuff was too spread out.
thanks again
"Bob Barrows [MVP]"
news:%23WVDkj%23fGHA.1260@TK2MSFTNGP05.phx.gbl...
> Oh, yes. No problem.
> INSERT INTO newdb..table SELECT * FROM olddb..table
>
> Why do you need this to happen via asp? This does not seem like an
> activity that should happen often enough to need to to be triggered by an
> asp page. Why wouldn't you use a DTS package to move the table and data?
>
> Oh, you can use the wizard to create a DTS package which can be triggered
> to run via ASP:
> http://www.sqldts.com/default.aspx?207,1
>
> But again, unless you are creating an online substitute for Enterprise
> Manager, I would be hesitant about tying up IIS resources with this
> activity.
>
>
> Jeff wrote:
>> ok. so if i create the tables in the target db, i should be able to
>> move the data between diff db's??
>>
>>
>> "Bob Barrows [MVP]"
>> news:%23rpQmX5fGHA.4900@TK2MSFTNGP02.phx.gbl...
>>> Jeff wrote:
>>>> I need to move the enitre table, Indexes, constraints,
>>>> relationships, everything. worse case senario, i could build the
>>>> tables, and just move the data, but would rather do it all at once.
>>>
>>> There's no way to do this in one fell swoop, short of restoring a
>>> backup.
>>
>> ok. so if i create the tables in the target db, i should be able to
>> move the data between diff db's??
>>
>>
>>
>>
>>>
>>>>
>>>> "Bob Barrows [MVP]"
>>>> news:eKaM3$3fGHA.3364@TK2MSFTNGP05.phx.gbl...
>>>>> Jeff wrote:
>>>>>> I am using mssql db and access it via SQL server management
>>>>>> studio. is it possible to move a table from 1 db on my server to
>>>>>> another
>>>>>> db on my server?
>>>>>>
>>>>>> if it can't be done through that program, can it be done with
>>>>>> script? like select * from tablea
>>>>>>
>>>>>> and close the connection, then open the connection of the second
>>>>>> db.. and select it into there??
>>>>>
>>>>> It depends on what you need to move: just the data? or do you need
>>>>> the resulting table to have all the metadata as well? Indexes,
>>>>> constraints, relationships, etc.
>>>>>
>>>>> --
>>>>> Microsoft MVP - ASP/ASP.NET
>>>>> Please reply to the newsgroup. This email account is my spam trap
>>>>> so I don't check it very often. If you must reply off-line, then
>>>>> remove the "NO SPAM"
>>>
>>> --
>>> Microsoft MVP - ASP/ASP.NET
>>> Please reply to the newsgroup. This email account is my spam trap so
>>> I don't check it very often. If you must reply off-line, then remove
>>> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
Ah! So this isn't an asp question. In the future, this type of question
can go to one of the sqlserver groups.
Since asp is not involved, i would definitely use the DTS wizard to
export or import your table. There's a right-click-menu option (under
All Tasks when a table is right-clicked) in Enterprise Manager to launch
the wizard.
Jeff wrote:
> thanks bob,
> this will only happen once. i onle need to move the tables from one
> db to another one time.
>
> I am condensing my webs, and realized that stuff was too spread out.
>
> thanks again
>
>
> "Bob Barrows [MVP]"
> news:%23WVDkj%23fGHA.1260@TK2MSFTNGP05.phx.gbl...
>> Oh, yes. No problem.
>> INSERT INTO newdb..table SELECT * FROM olddb..table
>>
>> Why do you need this to happen via asp? This does not seem like an
>> activity that should happen often enough to need to to be triggered
>> by an asp page. Why wouldn't you use a DTS package to move the table
>> and data?
>>
>> Oh, you can use the wizard to create a DTS package which can be
>> triggered to run via ASP:
>> http://www.sqldts.com/default.aspx?207,1
>>
>> But again, unless you are creating an online substitute for
>> Enterprise Manager, I would be hesitant about tying up IIS resources
>> with this activity.
>>
>>
>> Jeff wrote:
>>> ok. so if i create the tables in the target db, i should be able to
>>> move the data between diff db's??
>>>
>>>
>>> "Bob Barrows [MVP]"
>>> news:%23rpQmX5fGHA.4900@TK2MSFTNGP02.phx.gbl...
>>>> Jeff wrote:
>>>>> I need to move the enitre table, Indexes, constraints,
>>>>> relationships, everything. worse case senario, i could build the
>>>>> tables, and just move the data, but would rather do it all at
>>>>> once.
>>>>
>>>> There's no way to do this in one fell swoop, short of restoring a
>>>> backup.
>>>
>>> ok. so if i create the tables in the target db, i should be able to
>>> move the data between diff db's??
>>>
>>>
>>>
>>>
>>>>
>>>>>
>>>>> "Bob Barrows [MVP]"
>>>>> news:eKaM3$3fGHA.3364@TK2MSFTNGP05.phx.gbl...
>>>>>> Jeff wrote:
>>>>>>> I am using mssql db and access it via SQL server management
>>>>>>> studio. is it possible to move a table from 1 db on my server to
>>>>>>> another
>>>>>>> db on my server?
>>>>>>>
>>>>>>> if it can't be done through that program, can it be done with
>>>>>>> script? like select * from tablea
>>>>>>>
>>>>>>> and close the connection, then open the connection of the second
>>>>>>> db.. and select it into there??
>>>>>>
>>>>>> It depends on what you need to move: just the data? or do you
>>>>>> need the resulting table to have all the metadata as well?
>>>>>> Indexes, constraints, relationships, etc.
>>>>>>
>>>>>> --
>>>>>> Microsoft MVP - ASP/ASP.NET
>>>>>> Please reply to the newsgroup. This email account is my spam trap
>>>>>> so I don't check it very often. If you must reply off-line, then
>>>>>> remove the "NO SPAM"
>>>>
>>>> --
>>>> Microsoft MVP - ASP/ASP.NET
>>>> Please reply to the newsgroup. This email account is my spam trap
>>>> so I don't check it very often. If you must reply off-line, then
>>>> remove the "NO SPAM"
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.