Copy data from one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy data from one table to another

Author

Topic

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2010-02-09 : 14:56:23

Hi, There are two tables Customer_New and Customer_Old. Customer_old table has less fields and Customer-New has more columns. I have to copy all data from customer_old to customer_new table. There is a column on customer_new table called MechantID which is a Primary key on Merchant table and is not null on Customer_new table. I have a generic script below but I am not sure how to accomplish this task. Please help me out on this issue.

INSERT INTO Customer_New(
CustID,
MerchantID, ---FK, This is not null and have to get from Marchent table
CustTitle,
Descr,
CName,
DAdded,
AddBy,
AdStats,
PhNumber,
HPhNumber,--Not Null
EmailID,
HEmailID,--Not Null
ExpireDate,
NotApproveCustID,--Not NULL
GeneralEmailID
)
SELECT
CID, -- This is CUstID
NULL, --- MerchantID should come from Merchant table
CustTitle,
Descr,
ContactDetails, ---CName
DAdded,
AddBy,
AdStats,
Null,
Null, ---This is HPhNumber which is not NULL
Null,
NuLL, ---HEmail which is not null
Null,
NULL, -- NotApproveCustID not null.
Null from customer_old

Please let me know if this is not clear.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 15:07:11

You have to provide some means to establishing the MerchantID for each customer.

Maybe you already have it on an Order Table (i.e. showing orders that a client has previously placed), or maybe there is an Excel spreadsheet with Customer ID and Merchant ID?


INSERT INTO Customer_New
( 
    CustID,
    MerchantID, ---FK, This is not null and have to get from Marchent table
    CustTitle, Descr,  CName, DAdded, AddBy, AdStats, PhNumber, HPhNumber,--Not Null 
    EmailID, HEmailID,--Not Null
    ExpireDate, NotApproveCustID,--Not NULL
    GeneralEmailID
)
SELECT 
CID, -- This is CUstID 
--DEL NULL, --- MerchantID should come from Merchant table
L.MerchantID,
CustTitle, Descr, ContactDetails, ---CName
DAdded, AddBy, AdStats,  Null,  Null, ---This is HPhNumber which is not NULL
Null, NuLL, ---HEmail which is not null 
Null, NULL, -- NotApproveCustID not null. 
Null 
from customer_old AS O
    LEFT OUTER JOIN SomeLookupTable AS L
        ON L.CustomerID = O.CID

You could also do:


SELECT 
CID, -- This is CUstID 
--DEL NULL, --- MerchantID should come from Merchant table
COALESCE(L.MerchantID, 1234),
CustTitle, Descr, ContactDetails, ---CName

where "1234" is some dummy Merchant ID for Customers who have no Merchant ID currently.

You MUST copy all customers from Old to New, but you may have to use a Dummy MerchantID if you cannot easily establish the correct MerchantID - then you can query MerchantID = 1234 and get those sorted out manually "after the fact"

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2010-02-09 : 15:12:04

Kristen,
I do not have a look up table: Tables i have are: customer Old table, customer new table and merchant table. On this tables, which one you think is a look up table.

Thanks

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 15:15:06

Is there anything in customer_OLD that indicates the Merchant ID?

If not, how do you propose to assign a MerchantID to each customer in Customer_NEW?

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2010-02-09 : 16:00:49

There is no merchant ID on Customer_old table. There is a separate table and now i am trying to establish a parent child relationship between Customer_New and Merchant table. Also, i want to put all data from customer_old table to a customer_new table. Is there a way that i can insert data from old to new table? I do not mind if i cannot insert the MerchantID from Merchant Table but i want to make sure that all data are inserted into a new table since there are few columns which do not accept NULL. Thanks for your help.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 17:09:06

" Is there a way that i can insert data from old to new table?"

My earlier query is designed to do that ...

"I do not mind if i cannot insert the MerchantID from Merchant Table"

Understood, but you said earlier "There is a column on customer_new table called MechantID which is a Primary key on Merchant table and is not null on Customer_new table" therefore you have to provide a value - hence my suggestion to provide a dummy value for rows where no value can be deduced.

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2010-02-11 : 09:14:38

Kristen:
There are other columns which are not NUL and has to be take care.
MerchantID, HPhNumber,HEmailID,NotApproveCustID are NOT NULL fields on Customer_NEW table. These columns are not present on CUSTOMER_OLD table. I am stuck how to insert other columns record from CUSTOMER_OLD table to Customer_NEW table. Thanks for your help and look forward your help on this task.

Kristen
Test

22859 Posts

Posted - 2010-02-11 : 09:29:01

You have a couple of choices:

1) Create the Customer_NEW table with a Default that is "acceptable". Lets say you use "-1", you can then report on rows which are "-1" and over time they can be corrected manually, as you can still report on any remaining records which are still "-1"

2) Do NOT create a default, but set the new column to suitable value (e.g. "-1") during the initial INSERT from CUSTOMER_OLD table. Benefit of this is that no NEW records are allowed to be created with those columns absent from any INSERT statements (i.e. Method 1 would continue to set those columns to "-1" for NEW records - if no value was provided for those columns).

I think (2) is preferable as it will cause any old INSERT statements [which fail to explicitly provide a value for the new columns] to raise an error, and thus those "bugs" will at least come to light.

Problem with (1) and (2) is that the user does not know whether a report includes all records, or if some still have "-1" and were not included in the report - thus the system cannot be relied on until 100% of the default "-1" data has been fixed. IME this tends to cause users to "not trust the stupid system"

3) Create some "lookup tables" and make the initial insert into Customer_NEW from CUSTOMER_OLD table by joining to those lookup tables so that proper values are provided from the get-go.

(3) would be my route of choice - even if users had to put the new values into Excel or somesuch to allow the initial migration to have valid data for all fields.

sql_newbie121
Yak Posting Veteran

52 Posts

Posted - 2010-02-11 : 09:48:18

Perfect, for now i would go with route 3. Now i understood a littlebit and i think i will be able to do this task. As always, thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources