August 4, 2017 at 1:15 pm
I have a weird problem. I have an Access database that has a table that contains about 50,000 lines that have charges and payments needing to go into SQL Server. The charges have been successfully moved into its own SQL table and I need to put the payments in a different table for accounting reasons. However, each payment inserted must reference a charge via a unique identifier I built from a concatenation. Each payment then has a concatenation ID that matches one for a charge. The data in Access has been move to Excel to create insert statements (hey it worked for getting the charges into SQL Server! :)) The columns available look like this:
ID.... PaymentDate.... PaymentAmount.... BillingDate.... BillingCode.... BilledAmount.... Concat....
479 2001-12-13 00:00:00.000 25.00 2001-12-30 00:00:00.000 3 25.00 4792001-12-13 00:00:00.00025
So ID, BillingDate, BillingCode, BilledAmount, and Concat are now successfully in SQL Server
I need to write a series of insert queries that put ID, PaymentDate, PaymentAmount, and Concat into the payments table.
My puzzle is how to associate the 'Concat ID' of each row.
Here is my basic T-SQL... any ideas? (THANKS!!!!):
SELECT [peopleId]
,[chargeAmount]
,[transactionDate]
,[Concat]
FROM [GCDF_DB].[dbo].[Charges] order by [transactionId] desc
INSERT INTO [GCDF_DB].[dbo].[Payments]
([peopleId]
,[amount]
,[transactionDate]
,[Concat]) WHERE.....
August 4, 2017 at 1:34 pm
briancampbellmcad - Friday, August 4, 2017 1:15 PMI have a weird problem. I have an Access database that has a table that contains about 50,000 lines that have charges and payments needing to go into SQL Server. The charges have been successfully moved into its own SQL table and I need to put the payments in a different table for accounting reasons. However, each payment inserted must reference a charge via a unique identifier I built from a concatenation. Each payment then has a concatenation ID that matches one for a charge. The data in Access has been move to Excel to create insert statements (hey it worked for getting the charges into SQL Server! :)) The columns available look like this:
ID.... PaymentDate.... PaymentAmount.... BillingDate.... BillingCode.... BilledAmount.... Concat....
479 2001-12-13 00:00:00.000 25.00 2001-12-30 00:00:00.000 3 25.00 4792001-12-13 00:00:00.00025So ID, BillingDate, BillingCode, BilledAmount, and Concat are now successfully in SQL Server
I need to write a series of insert queries that put ID, PaymentDate, PaymentAmount, and Concat into the payments table.
My puzzle is how to associate the 'Concat ID' of each row.
Here is my basic T-SQL... any ideas? (THANKS!!!!):
SELECT [peopleId]
,[chargeAmount]
,[transactionDate]
,[Concat]
FROM [GCDF_DB].[dbo].[Charges] order by [transactionId] descINSERT INTO [GCDF_DB].[dbo].[Payments]
([peopleId]
,[amount]
,[transactionDate]
,[Concat]) WHERE.....
Sorry no idea based on what you provided so far.
Suggest you start with some detail on the original Access table...some detail on what you have managed to split out so far and what you are expecting as final results.
here is a good pointer that will help yu provide detail required
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply