TRUNCATE and INSERT INTO SQL table DYNAMICALLY based on columns in SQL table.
Hello Experts,
I have a SQL table. See attached copy.
I have multiple companies in companyname column with each corresponding Select statements from each dbo. files table. Each company have exactly the same column fields. I want to insert each company "SelectFromTable" column into table in "InsertIntoTable" column dynamically. But before inserting into table, I want to delete the table first dynamically.
This is what I want to accomplish with the query:
Can this be possible?
Thank you again for all your help.
TRUNCATEandINSERTtableDYNAMICALLY.xlsx
I have a SQL table. See attached copy.
I have multiple companies in companyname column with each corresponding Select statements from each dbo. files table. Each company have exactly the same column fields. I want to insert each company "SelectFromTable" column into table in "InsertIntoTable" column dynamically. But before inserting into table, I want to delete the table first dynamically.
This is what I want to accomplish with the query:
Select "TrucateTable" , "InsertIntoTable" from dbo.DynamicTable
Can this be possible?
Thank you again for all your help.
TRUNCATEandINSERTtableDYNAMICALLY.xlsx
Try this:
DECLARE @SQL VARCHAR(MAX);
DECLARE @SelectFromTable VARCHAR(128) = 'AAAfiles';
DECLARE @InsertIntoTable VARCHAR(128) = 'BalanceAmtTable';
SET @SQL = 'TRUNCATE TABLE dbo.' + @InsertIntoTable +'; INSERT INTO dbo.' + @InsertIntoTable + ' (Amt, BalanceAmt, Reasons, DateDue) SELECT Amt, BalanceAmt, Reasons, DateDue FROM dbo.' + @SelectFromTable + ' WHERE BalanceAmt >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -3, current_timestamp)), 0) AND Amt = 0;'
EXECUTE (@SQL);
Are you wanting to pull the names from another table (e.g. DynamicTable) or just supply the from and to table names?
I'm not sure if that what you mean but you could truncate the table for the first time :
Then perform an insert query from table to table multiple time as much as companies you've :
The insert query could be saved as a PROCEDURE and then pass the table name dbo.XXXfiles as parameter to it.
TRUNCATE TABLE dbo.BalanceAmtTable
Then perform an insert query from table to table multiple time as much as companies you've :
INSERT INTO dbo.BalanceAmtTable(Amt,BalanceAmt,Reasons,DateDue)
SELECT Amt, BalanceAmt, Reasons, DateDue
FROM dbo.AAAfiles
WHERE BalanceAmt >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -3, current_timestamp)), 0) AND Amt = 0
The insert query could be saved as a PROCEDURE and then pass the table name dbo.XXXfiles as parameter to it.
@Doug Bishop:
I will test your SQL query and execute my Stored Procedure and I will let you know as soon as I finish.
Are you wanting to pull the names from another table (e.g. DynamicTable) or just supply the from and to table names?
I want to pull dynamically from dbo.DynamicTable.
@Zararia Acharki:
You are right. I can truncate the table for the first time. But I want to execute dynamically using SQL table dbo.DynamicTable using "SelectFromTable" column.
Thank you again for all your help.
I will test your SQL query and execute my Stored Procedure and I will let you know as soon as I finish.
Are you wanting to pull the names from another table (e.g. DynamicTable) or just supply the from and to table names?
I want to pull dynamically from dbo.DynamicTable.
@Zararia Acharki:
You are right. I can truncate the table for the first time. But I want to execute dynamically using SQL table dbo.DynamicTable using "SelectFromTable" column.
Thank you again for all your help.
So, you want to truncate once only and then load into your target table from all the tables in source table(s) listed in DynamicTable? Or, is your example incorrect and you will be loading into separate target tables. It does not make sense to truncate the table to load subsequent tables when you just loaded data.
In other words, per your direction above, the steps would be:
In other words, per your direction above, the steps would be:
- truncate BalanceAmtTable
- load BalanceAmtTable from AAAfiles
- truncate BalanceAmtTable
- load BalanceAmtTable from BBBfiles
- truncate BalanceAmtTable
- load BalanceAmtTable from CCCfiles
- ...
I am not a staunch nay-sayer regarding cursors as this is definitely a case for using one:
DECLARE @SQL VARCHAR(MAX);
DECLARE @SelectFromTable VARCHAR(128) = 'AAAfiles';
DECLARE @InsertIntoTable VARCHAR(128) = 'BalanceAmtTable';
DECLARE csrTables CURSOR LOCAL FAST_FORWARD FOR
SELECT SelectFromTable,
InsertIntoTable
FROM dbo.DynamicTable
ORDER BY SelectFromTable;
OPEN csrTables;
FETCH NEXT FROM csrTables INTO @SelectFromTable, @InsertIntoTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'TRUNCATE TABLE dbo.' + @InsertIntoTable +'; INSERT INTO dbo.' + @InsertIntoTable + ' (Amt, BalanceAmt, Reasons, DateDue) SELECT Amt, BalanceAmt, Reasons, DateDue FROM dbo.' + @SelectFromTable + ' WHERE BalanceAmt >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -3, current_timestamp)), 0) AND Amt = 0;'
EXECUTE (@SQL);
-- if there is other processing to do against the table you just loaded, it would go here
FETCH NEXT FROM csrTables INTO @SelectFromTable, @InsertIntoTable
END
CLOSE csrTables
DEALLOCATE csrTables
-- post processing
@DougBishop:
I just want to Truncate the Table first like this:
truncate table dbo.BalanceAmtTable. Since there is only table to delete and insert into.
then the next select query is:
Select [SelectFromTable], [InsertIntoTable] from dbo.DynamicTable
I just don't know the query syntax.
Thank you again.
I just want to Truncate the Table first like this:
truncate table dbo.BalanceAmtTable. Since there is only table to delete and insert into.
then the next select query is:
Select [SelectFromTable], [InsertIntoTable] from dbo.DynamicTable
I just don't know the query syntax.
Thank you again.
Very minor change to above code:
DECLARE @SQL VARCHAR(MAX);
DECLARE @SelectFromTable VARCHAR(128) = 'AAAfiles';
DECLARE @InsertIntoTable VARCHAR(128) = 'BalanceAmtTable';
DECLARE csrTables CURSOR LOCAL FAST_FORWARD FOR
SELECT SelectFromTable,
InsertIntoTable
FROM dbo.DynamicTable
ORDER BY SelectFromTable;
OPEN csrTables;
FETCH NEXT FROM csrTables INTO @SelectFromTable, @InsertIntoTable
IF @@FETCH_STATUS = 0
EXECUTE ('TRUNCATE TABLE dbo.' + @InsertIntoTable +'; ')
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT INTO dbo.' + @InsertIntoTable + ' (Amt, BalanceAmt, Reasons, DateDue) SELECT Amt, BalanceAmt, Reasons, DateDue FROM dbo.' + @SelectFromTable + ' WHERE BalanceAmt >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -3, current_timestamp)), 0) AND Amt = 0;'
EXECUTE (@SQL);
-- if there is other processing to do against the table you just loaded, it would go here
FETCH NEXT FROM csrTables INTO @SelectFromTable, @InsertIntoTable
END
CLOSE csrTables
DEALLOCATE csrTables
-- post processing
@DougBishop:
I tested the code above but I have syntax error:
Incorrect syntax near the keyword 'INSERT'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘AMT'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'.
Msg 156, Level 15, State 1, Line 1
Thank you again for your help.
I tested the code above but I have syntax error:
Incorrect syntax near the keyword 'INSERT'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘AMT'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WHERE'.
Msg 156, Level 15, State 1, Line 1
Thank you again for your help.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Here are the results of running the above code, as expected:
Src Amt BalanceAmt Reasons DateDue
AAA 0.00 2018-11-02 reason 2 - AAA 2018-12-31
AAA 123.45 2018-11-01 reason 1 - AAA 2018-12-31
BBB 0.00 2018-11-04 reason 2 - BBB 2018-12-31
BBB 987.65 2018-11-03 reason 1 - BBB 2018-12-31
CCC 777.77 2018-11-05 reason 1 - CCC 2018-12-31
CCC 888.88 2018-11-06 reason 2 - CCC 2018-12-31
Out 0.00 2018-11-02 reason 2 - AAA 2018-12-31
Out 0.00 2018-11-04 reason 2 - BBB 2018-12-31
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.