June 2, 2010 at 8:14 am
I have two tables eg namely Order_A and Order_B and it has data in it, now I want to transfer selected data from Order_B table to Order_A table but I dont want to loose data from Order_A table i.e, at last Order_A table will contain data of (Order_A + Order_B) how can I write this as a query, if I use UPDATE I will loose all content in Order_A and it will update content of Order_B in Order_A
will appreciate suggestions..
June 2, 2010 at 8:45 am
Just an FYI here. If you want traction from your post you will need to supply more info. These guys are quite incredible as a resource however they won’t waste their time if you don’t provide more information. We need the table names and columns. Also what your joining on or at least an example of what you've tried. If you right click the tables in query analyzer then hit generate script as insert. Cut and past that into here so we can see the table def. Otherwise you may be on your own.
With that being said. I'll give it a shot in the dark. The below is obviously insufficient because not only do I not know what “Selected date” is but I don’t know the column names and nor table names. Either way good luck.
Insert into Order_a
Select *
From order_b
June 2, 2010 at 9:15 am
ok I have a table named Order_A, which has data stored in it which has columns as shown in query
SELECT [OrderID]
,[OrderDate]
,[OrderStatusID]
,[PaymentTypeID]
,[PaymentStatusID]
,[ShippingID]
,[ShippingStatusID]
,[ShippingDate]
,[ShippingAmount]
,[TrackingNumber]
,[Passwd]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[Zip]
,[State]
,[Province]
,[Country]
,[Email]
,[BillingAddress]
,[BillingCity]
,[BillingState]
,[BillingProvince]
,[BillingZip]
,[BillingCountry]
,[ApprovedCode]
,[TransactionResult]
,[AuthCode]
,[RespMsg]
,[CustomNotes]
,[Phone]
,[CustomerStatusId]
,[CustomerNotes]
,[MarkFlag]
,[UserID]
,[Discount]
,[DealID]
,[DealPrice]
,[Referrer]
,[PaymentStatus]
,[PaymentType]
,[PaymentDate]
,[PendingReason]
,[TransactionID]
,[PayerEmail]
,[PayerStatus]
,[PayerID]
,[CODStatusID]
,[CompanyName]
,[Address2]
,[DealerInvoice]
,[CustomerResponse]
,[CardCVV]
,[Warranty]
,[Insurance]
,[GiftWrapping]
,[CleaningKit]
,[CouponDiscount]
,[priorityStatus]
,[chargedCC]
,[chargedate]
,[AmtRefunded]
,[uon]
,[paymentstatus_final]
,[flag]
,[checkstatusID]
,[esdate]
,[lmdate]
,[deletedate]
,[invoiceflag]
,[unsubscribeflag]
,[dailyreportflag]
,[folderemailsend]
,[wholesaleuserid]
,[amtrefundedflag]
,[folderemailsendenabled]
,[wpaid]
,[canceldate]
,[createdbyid]
,[createdby]
FROM [dbo].[Order_A]
GO
and Order_B table has following below columns which also consists of some data
SELECT [OrderID] (identity column)
,[OrderDate]
,[OrderStatusID]
,[PaymentTypeID]
,[PaymentStatusID]
,[ShippingID]
,[ShippingStatusID]
,[ShippingDate]
,[ShippingAmount]
,[TrackingNumber]
,[Passwd]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[Zip]
,[State]
,[Province]
,[Country]
,[Email]
,[BillingAddress]
,[BillingCity]
,[BillingState]
,[BillingProvince]
,[BillingZip]
,[BillingCountry]
,[ApprovedCode]
,[TransactionResult]
,[AuthCode]
,[RespMsg]
,[CustomNotes]
,[Phone]
,[CustomerStatusId]
,[CustomerNotes]
,[MarkFlag]
,[UserID]
,[Discount]
,[DealID]
,[DealPrice]
,[Referrer]
,[PaymentStatus]
,[PaymentType]
,[PaymentDate]
,[PendingReason]
,[TransactionID]
,[PayerEmail]
,[PayerStatus]
,[PayerID]
,[CODStatusID]
,[CompanyName]
,[Address2]
,[DealerInvoice]
,[CustomerResponse]
,[CardCVV]
,[Warranty]
,[Insurance]
,[GiftWrapping]
,[CleaningKit]
,[CouponDiscount]
,[priorityStatus]
,[chargedCC]
,[chargedate]
,[AmtRefunded]
,[uon]
,[paymentstatus_final]
,[flag]
,[checkstatusID]
,[esdate]
,[lmdate]
,[deletedate]
,[invoiceflag]
,[unsubscribeflag]
,[dailyreportflag]
,[folderemailsend]
,[wholesaleuserid]
,[amtrefundedflag]
,[folderemailsendenabled]
,[wpaid]
,[canceldate]
,[createdbyid]
,[createdby]
,[AddressTwo]
,[CONumber]
,[AdminNotes]
,[ItemsShipped]
,[WSEmailLastSent]
,[WaitRespEmail]
,[AddressOne]
,[BillingAddress2]
FROM [dbo].[Order_B]
GO
now I want to transfer data from Order_B to Order_A such that the table will result in Order_A = Order_A + Order_B and after transferring data from Order_B to Order_A the Order_B table will not consists the data which is transferred...
I hope this understands
June 2, 2010 at 9:20 am
i see. And just to clarify you basically want to move whats in b to a. There will be no actual addition correct?
June 2, 2010 at 9:39 am
yes there is no actual addition I just want to move selected b data to a after moving data the table b should not hold whatever is moved to a ..
June 2, 2010 at 10:44 am
you can use an Insert... Select.. combined with a delete from tableb at the end.
June 2, 2010 at 10:45 am
Try this method:
create table #Table_A
( id int not null identity (1,1)
,val varchar(30)
)
create table #Table_B
( id int not null identity (1,1)
,val varchar(30)
)
insert into #Table_A
select 'astra'
union select 'azbuka'
union select 'arbuz'
insert into #Table_B
select 'barsuk'
union select 'begemot'
union select 'bezdelnik'
union select 'osel'
union select 'ostalop'
union select 'oluh'
-- here we delete some records from Table_B and insert whatever deleted into Table_A
delete #Table_B
output deleted.val into #Table_A (val)
where val like 'b%'
I am not sure what you want to do with the values in the identity column?
Do you want to re-use identity values of the table you are deleting from?
If yes, then you must ensure that these values are not present in the destination table and you must turn identity insert ON for the destination table.
June 2, 2010 at 10:52 am
elutin (6/2/2010)
Try this method:
create table #Table_A
( id int not null identity (1,1)
,val varchar(30)
)
create table #Table_B
( id int not null identity (1,1)
,val varchar(30)
)
insert into #Table_A
select 'astra'
union select 'azbuka'
union select 'arbuz'
insert into #Table_B
select 'barsuk'
union select 'begemot'
union select 'bezdelnik'
union select 'osel'
union select 'ostalop'
union select 'oluh'
-- here we delete some records from Table_B and insert whatever deleted into Table_A
delete #Table_B
output deleted.val into #Table_A (val)
where val like 'b%'
I am not sure what you want to do with the values in the identity column?
Do you want to re-use identity values of the table you are deleting from?
If yes, then you must ensure that these values are not present in the destination table and you must turn identity insert ON for the destination table.
I dont understand this part
insert into #Table_A
select 'astra'
union select 'azbuka'
union select 'arbuz'
insert into #Table_B
select 'barsuk'
union select 'begemot'
union select 'bezdelnik'
union select 'osel'
union select 'ostalop'
union select 'oluh'
June 2, 2010 at 10:54 am
That is easy!
This part supposed to be provided by You to help set up your case tables and sample data.
😀
June 2, 2010 at 11:06 am
basically what I am doing in here is Order_B is my live table which consists of the Order Details Data as shown in query, what I am doing is I am moving the old orders which are order dated from NOV 2009 and older to table Order_A, after moving this older orders to Order_A table Order_B wont consists moved data..
June 2, 2010 at 11:14 am
Basically, my sample does exactly the same:
It copies some records from Table_B to Table_A (condition is in WHERE clause) and at the same time it deletes these copied records from Table_B.
However strictly speaking: It does delete records from Table_B and does insert these deleted records into Table_A.
I hornestly believe that you should be able to apply the above method in your case.
June 2, 2010 at 11:21 am
the below will work but the order id's will be recreated in table a and therefore they will change. Is that a problem? Do you want to keep the order id's from table b when u add the records to table a?
insert into [dbo].[Order_A] ([OrderDate]
,[OrderStatusID]
,[PaymentTypeID]
,[PaymentStatusID]
,[ShippingID]
,[ShippingStatusID]
,[ShippingDate]
,[ShippingAmount]
,[TrackingNumber]
,[Passwd]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[Zip]
,[State]
,[Province]
,[Country]
,[Email]
,[BillingAddress]
,[BillingCity]
,[BillingState]
,[BillingProvince]
,[BillingZip]
,[BillingCountry]
,[ApprovedCode]
,[TransactionResult]
,[AuthCode]
,[RespMsg]
,[CustomNotes]
,[Phone]
,[CustomerStatusId]
,[CustomerNotes]
,[MarkFlag]
,[UserID]
,[Discount]
,[DealID]
,[DealPrice]
,[Referrer]
,[PaymentStatus]
,[PaymentType]
,[PaymentDate]
,[PendingReason]
,[TransactionID]
,[PayerEmail]
,[PayerStatus]
,[PayerID]
,[CODStatusID]
,[CompanyName]
,[Address2]
,[DealerInvoice]
,[CustomerResponse]
,[CardCVV]
,[Warranty]
,[Insurance]
,[GiftWrapping]
,[CleaningKit]
,[CouponDiscount]
,[priorityStatus]
,[chargedCC]
,[chargedate]
,[AmtRefunded]
,[uon]
,[paymentstatus_final]
,[flag]
,[checkstatusID]
,[esdate]
,[lmdate]
,[deletedate]
,[invoiceflag]
,[unsubscribeflag]
,[dailyreportflag]
,[folderemailsend]
,[wholesaleuserid]
,[amtrefundedflag]
,[folderemailsendenabled]
,[wpaid]
,[canceldate]
,[createdbyid]
,[createdby]
SELECT [OrderDate]
,[OrderStatusID]
,[PaymentTypeID]
,[PaymentStatusID]
,[ShippingID]
,[ShippingStatusID]
,[ShippingDate]
,[ShippingAmount]
,[TrackingNumber]
,[Passwd]
,[FirstName]
,[LastName]
,[Address]
,[City]
,[Zip]
,[State]
,[Province]
,[Country]
,[Email]
,[BillingAddress]
,[BillingCity]
,[BillingState]
,[BillingProvince]
,[BillingZip]
,[BillingCountry]
,[ApprovedCode]
,[TransactionResult]
,[AuthCode]
,[RespMsg]
,[CustomNotes]
,[Phone]
,[CustomerStatusId]
,[CustomerNotes]
,[MarkFlag]
,[UserID]
,[Discount]
,[DealID]
,[DealPrice]
,[Referrer]
,[PaymentStatus]
,[PaymentType]
,[PaymentDate]
,[PendingReason]
,[TransactionID]
,[PayerEmail]
,[PayerStatus]
,[PayerID]
,[CODStatusID]
,[CompanyName]
,[Address2]
,[DealerInvoice]
,[CustomerResponse]
,[CardCVV]
,[Warranty]
,[Insurance]
,[GiftWrapping]
,[CleaningKit]
,[CouponDiscount]
,[priorityStatus]
,[chargedCC]
,[chargedate]
,[AmtRefunded]
,[uon]
,[paymentstatus_final]
,[flag]
,[checkstatusID]
,[esdate]
,[lmdate]
,[deletedate]
,[invoiceflag]
,[unsubscribeflag]
,[dailyreportflag]
,[folderemailsend]
,[wholesaleuserid]
,[amtrefundedflag]
,[folderemailsendenabled]
,[wpaid]
,[canceldate]
,[createdbyid]
,[createdby]
FROM [dbo].[Order_B]
June 2, 2010 at 11:27 am
Eugene Elutin is right, you should be able to deduce what needs to happen from what he supplied. However, everyone has to start from somewhere and after this issue is resolved for you I strongly suggest you continue working on their suggestions until you see where their coming from. This site can accelerate a sql dev skill set dramatically as long as you persevere.
June 2, 2010 at 11:51 am
Eugene Elutin (6/2/2010)
Basically, my sample does exactly the same:It copies some records from Table_B to Table_A (condition is in WHERE clause) and at the same time it deletes these copied records from Table_B.
However strictly speaking: It does delete records from Table_B and does insert these deleted records into Table_A.
I hornestly believe that you should be able to apply the above method in your case.
can you please be more specific in that part select union " " I still dont understand what I have to do in it..
June 2, 2010 at 2:04 pm
biren (6/2/2010)
Eugene Elutin (6/2/2010)
Basically, my sample does exactly the same:It copies some records from Table_B to Table_A (condition is in WHERE clause) and at the same time it deletes these copied records from Table_B.
However strictly speaking: It does delete records from Table_B and does insert these deleted records into Table_A.
I hornestly believe that you should be able to apply the above method in your case.
can you please be more specific in that part select union " " I still dont understand what I have to do in it..
biren, you don't need to do it at all as, I believe, you already have real data in your real tables. I have used it to populate tables with some data for a sample I've gave you and to demonstrate what should be provided together with a questions you ask on this forum. Setup of tables and sample data minimizes time the helper need to spend on your problem. Some experts here are quite busy with their own work, so they will not even look in your problem if you not providing setup!
So, when you execute my sample code, you can realise what will happen and how data will be moved from Table_B to Table_A. To look it in more details, execute sample query by query checking what table looks like after each.
Based on the sample, you should be able to write similar query for your real tables.
You haven't answered the questino regarding the ID values from you IDENTITY column? Should they be regenerated when records are moved from your Order_B table (based on the current next availbale identity value in table Order_A) or they should stay as they were in Order_B?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply