Copying Table

I need to manipulate an existing Table. But before doing so I want to create
a back-up, TEMP table and copy the contents just to protect myself.
Is there an easy way to do this in SQL Server Enterprise Manager or do I
have to write SQL Queries in order to accomplish this?
I am from the DB2 world and I know in the DB2 world we would SHOW the table,
copy and paste the columns and their attributes and then paste them, rename
the table, and then accomplish the task.
I am somewhat new to the SQL Server world so can the experts out there tell
me the most efficient way to to this in the SQL Server world?
Thanks in advance!Right click on the table name then select export data. Follow the wizard to
create a DTS package.
--
Thomas
"wnfisba" wrote:

> I need to manipulate an existing Table. But before doing so I want to crea
te
> a back-up, TEMP table and copy the contents just to protect myself.
> Is there an easy way to do this in SQL Server Enterprise Manager or do I
> have to write SQL Queries in order to accomplish this?
> I am from the DB2 world and I know in the DB2 world we would SHOW the tabl
e,
> copy and paste the columns and their attributes and then paste them, renam
e
> the table, and then accomplish the task.
> I am somewhat new to the SQL Server world so can the experts out there tel
l
> me the most efficient way to to this in the SQL Server world?
> Thanks in advance!|||Couple of options:
1. DTS the data out of the table i.e., to a file
2. BCP (out) the data out of the table i.e., to a file
3. Create a copy of the table in the same or different database (i.e.,
SELECT... INTO)
HTH
Jerry
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:4B937AAC-FD59-4C86-847A-10FE52973ABA@.microsoft.com...
>I need to manipulate an existing Table. But before doing so I want to
>create
> a back-up, TEMP table and copy the contents just to protect myself.
> Is there an easy way to do this in SQL Server Enterprise Manager or do I
> have to write SQL Queries in order to accomplish this?
> I am from the DB2 world and I know in the DB2 world we would SHOW the
> table,
> copy and paste the columns and their attributes and then paste them,
> rename
> the table, and then accomplish the task.
> I am somewhat new to the SQL Server world so can the experts out there
> tell
> me the most efficient way to to this in the SQL Server world?
> Thanks in advance!sql

Copying deleted into temp table in trigger

For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' or
'deleted' tables

This seems like a weird error, since I am not actually doing anything
to the inserted or deleted tables, I am just trying to make a temp
copy.

I have another workaround but I am just curious why this happens.

Thanks,
RebeccaRebecca Lovelace (usagikawai@.yahoo.com) writes:
> For some reason in Enterprise Manager for SQL Server 2000, I cannot
> put the following line into a trigger:
> select * into #deleted from deleted
> When I hit the Apply button I get the following error:
> Cannot use text, ntext, or image columns in the 'inserted' or
> 'deleted' tables
> This seems like a weird error, since I am not actually doing anything
> to the inserted or deleted tables, I am just trying to make a temp
> copy.
> I have another workaround but I am just curious why this happens.

The message is very clear: there is a text, image, or ntext column in
your table, and cannot access that column. And since SELECT * implies
all columns, you access that column.

On another note, I fond recently that "SELECT * INTO #deleted FROM deleted"
in a trigger can be detrimental to performance. In my case, I was
running a one-by-one processing in a long transaction, and one table
had a trigger with a SELECT INTO like this. I had about given up to
get better speed, when I found that taking out the SELECT INTO and
using "inserted" directly gave a tremendous boost,

The reason this was such a winner, was that the locks on the system
tables in tempdb needed for all these temp tables were eating
resources. I also found that SELECT INTO #temp required more locks and
resources than CREATE TABLE #temp did. But there is a better alternative:
table variables, they don't need any tempdb locks at all.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> The message is very clear: there is a text, image, or ntext column in
> your table, and cannot access that column. And since SELECT * implies
> all columns, you access that column.

It would be clear, if there were any of those types of columns in my
table. But there aren't. It's just varchars and ints.

Does this work for you in SQL Server 2000?

It's more of a matter of curiousity at this point, I know this isn't
the best way to go, I just want to know why I can't do it.

Rebecca|||Rebecca Lovelace (usagikawai@.yahoo.com) writes:
>> The message is very clear: there is a text, image, or ntext column in
>> your table, and cannot access that column. And since SELECT * implies
>> all columns, you access that column.
> It would be clear, if there were any of those types of columns in my
> table. But there aren't. It's just varchars and ints.

That sounds very strange. I'm afraid that I don't have any answer. Is
possible for you to produce a script with a CREATE TABLE statement and
a CREATE TRIGGER that demonstrates the problem? In such case, I could
bring it up with Microsoft.

> Does this work for you in SQL Server 2000?

Yes, I have used SELECT * FROM #deleted FROM deleted in triggers with
success.

Well, success and success I have ran into two problems, but I have not
gotten that weird error message you got.

One problem I have mentioned: performance. The other problem may be
worth mentioning too. Just like you I called the temp table #deleted.
But then I had a trigger that updated another table which did the same
thing. This caused problems because when the second trigger was compiled,
#deleted already existed, but with different columns. So *if* you
this kind of thing, don't call the temp tables #inserted and #deleted,
but use some part of the table name to get a unique name.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copy Tabular Structure into a temp table

Hello Room,
M stuck with an issue with SQL Query. My requirement is to create a temporary table with the out put of "SELECT" Clause........

Like......
SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC

This is my "Select" Query and all I want is to store the out put in a #table. Now one way is to craete #tmp_table with the fields same as the out put column of the "SELECT" Clause....... But I want it in direct fashion... dat means without Creating the #tmp_table directly.........

I tried with "CREATE TBALE #tmp_table AS SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC"........ But it dini work

Please help me out in this regard.....SELECT NC_ID,
AUD_PLAN_ID,
AUD_CLAUSE_ID
into
#tmp_table
FROM AUD_NC|||Thankx Mallier........... It works........:)

Copy Table Over

Hi everone,
I have one table called temp and I am copying its content over to another table called final. The final table has 2 more coloumns than the temp table, 1 of which is a primary key, and the other one is calculated. What is the easiest way I can do this?

Thank you for the answer.

Try this:

Select *, One_column,Second_column into final from temp

Copy rows of one table to another table

I have four tables Candidate,Resume,Owner and Temp.

I want to search the freetext in Resume table and find the candidateid of the

respective candidates (satisfying search)and store those candidateid's into Table "Temp".

So that when doing an iterative search i may not seach through the entire resume table again.

Right now i am using "contain" function for doing iterative search by using "x and y".

this searches all the resumes of the candidate again.

By storing them inthe Temp table i want to restrict the new search only to the

resume selected in the search before.

For that i want to store the person who searched along with the candidateid

satisfying the search requirement.

Please help.

Thanks for the reply.

With regards

Girish R.

Is this something you planned to do with SSIS? If not, we could redirect this question to some other forum.

Thanks.

|||

I want to use stored procedure for this...

SSIS i am not aware.

But any suggestion are welcomed.

|||Moving to the more appropriate forum...