Need help copying one table to another. Should be easy.
-
You guys let me know if this is a newbie question. It probably will be.
Anyway, I'm trying to copy all the data from one table into another. I use the following but only 11 records are copied. I can't seem to understand why. There are 155 records in the original table.
insert into table2 select * from table1
Any ideas?
-
Paul Greenwood
-
try this one, but your query should be work.
select * into Table2 From Table1
-
Is there an error message or a trigger on that table??
-
Hi!!
i dont think so only 11 records are copied out of 155 thats not possible as it is bulk insert statement either all records should copy or no records copied giving some error!!!!!!!!!!!!!!!!!
So check ur database and tables carefully!!!!!!!!!!!!!!
Regards
Shashank
Regards,
Papillon
-
Are you replacing the target table or appending to it? If replacing are you using "TRUNCATE TABLE TABLE2" first? If appending, is there a Unique Primary Key on TABLE2 that could be preventing records from being copied if there are duplicate keys....
-
It's the weirdest thing. even using "select * into table2 from table1", I only get 11 records in my table.
I get no errors either. I'm typing it into the SQL Server Database Manager and it shows the command completed without error. It's the same when I put it into my code.
My provider is wanting to charge me $135/hr to debug this though it appears there may be a server setting issue or something. Is it possible it's just a server setting? Anyone know of a server only allowing x number of records to be copied at a time?
Thank you all for your kind help!
-
Paul Greenwood
-
Back to the begining, how many rows do you get when you do the select only?
-
Thanks for continuing to help RGR'us. I get 155 records from the select only. When I look at the actual records returned, I stop at 20 or so counting and issued "select count(*) from table1". This returned "155".
-
Paul Greenwood
-
Check the schema for Table 2 to see if there's an "On Insert" trigger that may be suppressing records (i.e. duplicate keys)...
-
Also check if there is a unique index that ignores duplicates.
-
I'm back....
The $135/hr consultant is telling me that when copying a table as I'm doing, the columns have to be in the same order.
Is this true?
If so, how do I ensure order when creating a table? I'm using something similar to the following (but 5x longer) and I get a different column order nearly every time I run it:
CREATE TABLE testtable
([userid] varchar(31) not null default 0 primary key,
[userkey] varchar(31) not null default 0,
[startdate] varchar(16) not null default 0,
[expirationdate] varchar(16) not null default 0,
[uses] varchar(8) not null default 0,
[users] varchar(4) not null default 0,
[mode] varchar(4) not null default 0,
[checksum] varchar(8) not null default 0,
[userpassword] varchar(31) null default 0);
Thanks again for your help!
-
Paul Greenwood
-
The best way to ensure your insert runs smoothly is to name the source and target columns:
Insert into Table1 (col1, col2, col3...coln)
Select (cola, colb, colc...colz) from Table2
Making sure the columns match up.
-
He is right about that and rschaeferhig's tip is actually a best practice that you should follow all the time. However I don't really see how that could have stopped 90% of the rows to be inserted in the table.
-
I found the problem with onliy 11 rows being copied. It seems to lie in the SQL Server Administrator my host provides online to execute single commands and test. When I execute the same copy command outside of that utility I don't have the problem. For example, it works fine in Multiple Database Query Analyzer.
So, back to the table creation and column ordering. Is there no way to guarantee order when I create the table so I can use "*" later?
Thanks again, and again, and again.....
-
Paul Greenwood
-
The ONLY guarantee is to write them both for the insert and the select.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply