Copying records from one table to an existing table

Copying records from one table to an existing table

Post by darre » Sun, 10 Aug 2008 03:26:06


It seems like this should be obvious and that I should know how to do
this...but I'm stumped.

I have a table of legacy data with about 10000 records. I want to copy SOME
of the fields from each of these records and input them as new records into
an already existing table.

This is what I have:

INSERT INTO TEST_table (application, title, postdate, source, maintext)
SELECT 'newsclip', title, postdate, source, HTMLPage
FROM newsclipitems
WHERE fileid = 1

If I run that inside of Enterprise Manager, it says one row was effected and
appears to have run succesfully, but, alas, no record is created in the
TEST_table table. Any idea what I'm doing wrong?

-Darrel
 
 
 

Copying records from one table to an existing table

Post by TheSQLGur » Sun, 10 Aug 2008 04:36:48

1) run just the select and see what you get out. If not what expect, fix
where clause

2) check test_table for a trigger to see if the data is being deleted.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

 
 
 

Copying records from one table to an existing table

Post by Aaron Bert » Sun, 10 Aug 2008 04:54:30


Did you mean Query Analyzer? If not, try it in a query window in Query
Analyzer. Don't use any of Enterprise Manager's grids or designers to try
and manipulate data.
 
 
 

Copying records from one table to an existing table

Post by darre » Sun, 10 Aug 2008 05:18:04


Just the select by itself works as expected.


Hmm...I'm not familiar with 'triggers'. How would I check for that? I doubt
there is one as I create the table.

-Darrel
 
 
 

Copying records from one table to an existing table

Post by darre » Sun, 10 Aug 2008 05:19:03

> Did you mean Query Analyzer?

Umm....well, when you open a table in Enterprise Manager and click on the
SQL PANE icon to get the SQL pane to appear above the table.

-Darrel
 
 
 

Copying records from one table to an existing table

Post by Aaron Bert » Sun, 10 Aug 2008 05:23:36

In Enterprise Manager, click on Tools / Query Analyzer instead. This is a
much better place to write SQL queries.

The one you are using, for example, will not run an INSERT... I think it
strips the insert portion off and just runs the SELECT. When you run it, do
you get data in the bottom pane? And INSERT statement shouldn't do that.



On 8/8/08 4:19 PM, in article uNvKBQZ# XXXX@XXXXX.COM ,
 
 
 

Copying records from one table to an existing table

Post by darre » Sun, 10 Aug 2008 05:36:25

> The one you are using, for example, will not run an INSERT... I think it

I do not get data, but a pop-up confirmation that 1 row was effected (which
is to be expected).

If I run it in Query Analyzer, same thing. The bottom pane returns '(1
row(s) affected)' when I run it, but if I open said TEST_table and return
all rows, there are no rows returned.

So, it seems to be doing SOMETHING, but not sure what.

-Darrel
 
 
 

Copying records from one table to an existing table

Post by Aaron Bert » Sun, 10 Aug 2008 05:39:34

Are you sure you don't have multiple copies of TEST_table (e.g. different
owners, or created in different databases)?


On 8/8/08 4:36 PM, in article u6qpuZZ# XXXX@XXXXX.COM ,
 
 
 

Copying records from one table to an existing table

Post by darre » Sun, 10 Aug 2008 05:56:13

> Are you sure you don't have multiple copies of TEST_table (e.g. different

Aha! Just one table, but was created as DBO. I wasn't running the query as
DBO.

So, not sure what it was doing, but once I told it to insert into
dbo.TEST_table, then it worked!

Thanks!

-Darrel
 
 
 

Copying records from one table to an existing table

Post by Aaron Bert » Sun, 10 Aug 2008 06:03:07

It means you have another table called Test_Table, but with a different
owner. Always specify table prefix. And my recommendation still stands,
use Query Analyzer to view/manipulate data, not Open Table. Just take a
look at all of the bugs and suggestions filed against Open Table, and this
is mostly against SQL Server 2005 and 2008, where it is supposedly better
than it was in 2000:

http://www.yqcomputer.com/
ry="open+table"




On 8/8/08 4:56 PM, in article #ebrykZ# XXXX@XXXXX.COM ,
 
 
 

Copying records from one table to an existing table

Post by Aaron Bert » Sun, 10 Aug 2008 06:31:14

Hit F8 in Query Analyzer, then expand your database, and then expand the
Tables node. The Object Explorer there should list the tables in multiple
groups, organized by owner alphabetically, and then by table name
alphabetically. I am working from memory as I don't have the tool handy
right now.

Note that this "owner" concept goes away in SQL Server 2005 and beyond;
replaced by "schema."




On 8/8/08 5:23 PM, in article OjHe8zZ# XXXX@XXXXX.COM ,