Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Logic question regarding inserting of rows from 1 table to another. Expand / Collapse
Author
Message
Posted Friday, November 1, 2013 10:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
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.
Post #1510698
Posted Friday, November 1, 2013 10:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,383, Visits: 9,951
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

Post #1510706
Posted Friday, November 1, 2013 10:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
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.
Post #1510710
Posted Friday, November 1, 2013 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,383, Visits: 9,951
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
Post #1510713
Posted Friday, November 1, 2013 10:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
Super, thanks so much for the information.
Post #1510715
Posted Friday, November 1, 2013 1:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
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.
Post #1510773
Posted Monday, November 4, 2013 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 5,383, Visits: 9,951
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
Post #1511009
Posted Monday, November 4, 2013 7:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 PM
Points: 7,738, Visits: 9,487
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
Post #1511103
Posted Monday, November 4, 2013 9:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 7:04 AM
Points: 72, Visits: 172
that is what i was needing. the not in was doing me in.

Thanks
Post #1511169
Posted Monday, November 4, 2013 2:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:43 AM
Points: 10, Visits: 74
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
Post #1511297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse