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:

Select "TrucateTable" , "InsertIntoTable" from dbo.DynamicTable

Open in new window


Can this be possible?

Thank you again for all your help.
TRUNCATEandINSERTtableDYNAMICALLY.xlsx
Queennie LAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Queennie LAuthor Commented:
Please help. Thank you.
Doug BishopDatabase DeveloperCommented:
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);

Open in new window

If you put it into a procedure, you can pass @SelectFromTable and @InsertIntoTable as parameters and remove them from the code above.
Doug BishopDatabase DeveloperCommented:
Are you wanting to pull the names from another table (e.g. DynamicTable) or just supply the from and to table names?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
Zakaria AcharkiAnalyst DeveloperCommented:
I'm not sure if that what you mean but you could truncate the table for the first time :

TRUNCATE TABLE dbo.BalanceAmtTable

Open in new window


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

Open in new window


The insert query could be saved as a PROCEDURE and then pass the table name dbo.XXXfiles as parameter to it.
Queennie LAuthor Commented:
@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.
Doug BishopDatabase DeveloperCommented:
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:
  1. truncate BalanceAmtTable
  2. load BalanceAmtTable from AAAfiles
  3. truncate BalanceAmtTable
  4. load BalanceAmtTable from BBBfiles
  5. truncate BalanceAmtTable
  6. load BalanceAmtTable from CCCfiles
  7. ...
Unless there is some other interim processing going on after the load, you are going to truncate after each (except the final) load which means you lose the data just loaded.
Doug BishopDatabase DeveloperCommented:
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

Open in new window

Queennie LAuthor Commented:
@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.
Doug BishopDatabase DeveloperCommented:
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

Open in new window

Queennie LAuthor Commented:
@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.
Doug BishopDatabase DeveloperCommented:
Not sure why. What version of SQL Server are you using? This should run in any version on or after 2008.
I used the following code to create test tables and it ran without error:
if object_id('dbo.DynamicTable') is not null drop table dbo.DynamicTable
if object_id('dbo.AAAFile', 'U') is not null drop table dbo.AAAFile
if object_id('dbo.BBBFile', 'U') is not null drop table dbo.BBBFile
if object_id('dbo.CCCFile', 'U') is not null drop table dbo.CCCFile
if object_id('dbo.OutputTable', 'U') is not null drop table dbo.OutputTable

create table dbo.DynamicTable (SelectFromTable sysname, InsertIntoTable sysname)
create table dbo.AAAFile (Amt numeric(12,2), BalanceAmt date, Reasons varchar(max), DateDue date)
create table dbo.BBBFile (Amt numeric(12,2), BalanceAmt date, Reasons varchar(max), DateDue date)
create table dbo.CCCFile (Amt numeric(12,2), BalanceAmt date, Reasons varchar(max), DateDue date)
create table dbo.OutputTable (Amt numeric(12,2), BalanceAmt date, Reasons varchar(max), DateDue date)
insert into dbo.DynamicTable values ('AAAFile','OutputTable'),('BBBFile','OutputTable'),('CCCFile','OutputTable')
insert into AAAFile values(123.45, '2018-11-01','reason 1 - AAA', '2018-12-31'),(0, '2018-11-02','reason 2 - AAA', '2018-12-31')
insert into BBBFile values(987.65, '2018-11-03','reason 1 - BBB', '2018-12-31'),(0, '2018-11-04','reason 2 - BBB', '2018-12-31')
insert into CCCFile values(777.77, '2018-11-05','reason 1 - CCC', '2018-12-31'),(888.88, '2018-11-06','reason 2 - CCC', '2018-12-31')

-- THIS IS THE CODE I PROVIDED YOU

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;'
	print @SQL
	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 - Display results and drop tables 
select 'AAA' as Src, * from AAAFile
union 
select 'BBB' as Src, * from BBBFile
union 
select 'CCC' as Src, * from CCCFile
union
select 'Out' as Src, * from OutputTable


if object_id('dbo.DynamicTable') is not null drop table dbo.DynamicTable
if object_id('dbo.AAAFile', 'U') is not null drop table dbo.AAAFile
if object_id('dbo.BBBFile', 'U') is not null drop table dbo.BBBFile
if object_id('dbo.CCCFile', 'U') is not null drop table dbo.CCCFile
if object_id('dbo.OutputTable', 'U') is not null drop table dbo.OutputTable

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperCommented:
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

Open in new window

Queennie LAuthor Commented:
@Doug Bishop:

I will test it and I will let you know.

Thank you again.
Queennie LAuthor Commented:
@Doug Bishop:

Thank you again for all your help.

To all Experts that helped me. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.