November 1, 2013 at 10:30 am
at various times i need to copy the rows to another table but it has minor differences.
table1
t1.f1 int,
t1.f2 char(25),
t1.f3 varchar(50),
t1.f4 smallint,
t1.f5 int,
t1.Identity int.
then
table2
t2.f1 int,
t2.f5 int,
t2.f4 smallint,
t2.f3 varchar(75),
t2.f6 varchar(100),
t2.Identity int.
My current plan is to determine which columns exist in both tables and then run an INSERT command based upon those columns.
I am familiar with issues dealing with the Identity columns and will exclude them from the matching logic but what other issues might I run into. What should I avoid doing.
I will be writing a Delphi application to do this and was wondering if T-SQL type command might do a better job of inserting rows from table1 to table2. Most of the tables I am dealing with have well over 400 columns up to 1024. Keep in mind that table2 already exists and might have rows already in it. Duplicates wont be an issue.
So i just need to mass copy rows from table1 to table2 regardless of the column order and to ignore the missing columns.
November 1, 2013 at 10:38 am
roy.tollison (11/1/2013)
at various times i need to copy the rows to another table but it has minor differences.table1
t1.f1 int,
t1.f2 char(25),
t1.f3 varchar(50),
t1.f4 smallint,
t1.f5 int,
t1.Identity int.
then
table2
t2.f1 int,
t2.f5 int,
t2.f4 smallint,
t2.f3 varchar(75),
t2.f6 varchar(100),
t2.Identity int.
My current plan is to determine which columns exist in both tables and then run an INSERT command based upon those columns.
I am familiar with issues dealing with the Identity columns and will exclude them from the matching logic but what other issues might I run into. What should I avoid doing.
I will be writing a Delphi application to do this and was wondering if T-SQL type command might do a better job of inserting rows from table1 to table2. Most of the tables I am dealing with have well over 400 columns up to 1024. Keep in mind that table2 already exists and might have rows already in it. Duplicates wont be an issue.
So i just need to mass copy rows from table1 to table2 regardless of the column order and to ignore the missing columns.
Full table DDL would have helped here, so that we could know whether the columns you're missing out are nullable or have default values defined. If they are or they do, this will work:
INSERT INTO
table2 (
f1
,f4
,f5
)
SELECT
f1
,f4
,f5
FROM
table1
November 1, 2013 at 10:45 am
That's pretty much what I have been coding in my application to do. Find all the matching columns then sorting them by name. I then got concerned about the sorting process. If by sorting the columns up causes my insert command to look more like this...
INSERT INTO
table2 (
f4
,f5
,f1
)
SELECT
f4
,f5
,f1
FROM
table1
I haven't found anything that says they have to be in column order but just wanted to make sure.
November 1, 2013 at 10:47 am
No, you can certainly put them in any order you like, and as far as I know, it doesn't make any difference to performance.
John
November 1, 2013 at 10:49 am
Super, thanks so much for the information.
November 1, 2013 at 1:02 pm
OK after going over this with the users i have been told that duplicate f1 (indexed unique) is possible. how would i go about changing the insert command to avoid getting duplicate error. I need it to not error out but to add all the rows that are not duplicates.
so t1.f1 = 1 thru 10000 but in t2.f1 they have added values 9000 thru 12000
i would need the insert command to add all of the rows 1 thru 8999
i have figured out the 'where t1.f1 not in (select t2.f1 from t2)' gets me around the duplicate error without bombing out. But how do i accomplish the same results if the unique index is multiple columns like f1 and f4 = index1
i need to avoid adding rows based upon index1 and not f1+f4.
thanks again.
November 4, 2013 at 2:09 am
I don't understand - if it's got a unique index, how can there be duplicates? Please will you post full table DDL (including indexes) and sample data in the form of INSERT statements?
John
November 4, 2013 at 7:30 am
roy.tollison (11/1/2013)
OK after going over this with the users i have been told that duplicate f1 (indexed unique) is possible. how would i go about changing the insert command to avoid getting duplicate error. I need it to not error out but to add all the rows that are not duplicates.so t1.f1 = 1 thru 10000 but in t2.f1 they have added values 9000 thru 12000
i would need the insert command to add all of the rows 1 thru 8999
i have figured out the 'where t1.f1 not in (select t2.f1 from t2)' gets me around the duplicate error without bombing out. But how do i accomplish the same results if the unique index is multiple columns like f1 and f4 = index1
i need to avoid adding rows based upon index1 and not f1+f4.
thanks again.
Not sure what "based upon index1 and not f1+f4" means, but for index in multiple columns like f1 and f4 you do it much as for the single column case, except that you have to use not exists instead of not in, something like
where not exists (select 1 from t1 inner join t2 on t1.f1=t2.f1 and t2.f4 = t1.f4)
Tom
November 4, 2013 at 9:25 am
that is what i was needing. the not in was doing me in.
Thanks
November 4, 2013 at 2:09 pm
If all columns are defined in both tables and all columns have default values, here is a possible solution.
Microsoft has a package for backwards compatibility with 2005, I think the install is named SQLServer2005_DTS.msi. There are many tools in the package, but one of them, which I use a lot, and which works on 2005, is called DTSWizard.exe. You can create a package with it and run as a job or do a one time run. It will copy one table to another table, and you can write a query to tell it what or how much or how to copy. You can get the download from download.microsoft.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply