Dynamically select load column names into select statement

Question

Post reply

Dynamically select load column names into select statement

Saran

Right there with Babe

Points: 753

November 16, 2022 at 12:54 pm

#4113710

Hi All,

I am looking for dynamically select load column names into select statement.


create table table1 (id int, name varchar(50),Dept varchar(50))
insert into table1 values (1,'a','IT')
insert into table1 values (2,'b','IT')

create table tbl_column_length (TABLE_SCHEMA varchar (500),TABLE_NAME varchar (500),COLUMN_NAME varchar (500),length bigint)

insert into tbl_column_length select 'dbo','table1','id',  max(len([id])) from table1
insert into tbl_column_length select 'dbo','table1','name',  max(len([name])) from table1
insert into tbl_column_length select 'dbo','table1','Dept',  max(len([Dept])) from table1

-- sample
select 
'bcp "select '+COLUMN_NAME+' from [db].dbo.'+name+'" queryout "G:\Test-Bak\Data\db'+'_Load\'+name+'_'+COLUMN_NAME+'.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\'+name+'_'+COLUMN_NAME+'_log.txt"' 
table_name,name,COLUMN_NAME from tbl_column_length C left join sysobjects  S on C.TABLE_NAME=S.name
where type='u' 
and length <100
and name in('table1')

--Original result:
bcp "select id from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1_id.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_id_log.txt"
bcp "select name from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1_name.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_name_log.txt"
bcp "select Dept from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1_Dept.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_Dept_log.txt"

--Needed result:
bcp "select id,name,Dept from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_log.txt"

-- bcp out from mssql
select 
'bcp "select '+COLUMN_NAME+' from [dbPainTrax_BHF_V].dbo.'+name+'" queryout "G:\Test-Bak\Data\dbPainTrax_BHF_V'+'_Load\'+name+'_'+COLUMN_NAME+'.txt" -S"WIN-DEV-SQL001\SQLEXPRESS,19667" -U"DBA_Admin" -P"Il0ve$ql@321" -n -c -t^| -T -o"G:\Test-Bak\Log\dbPainTrax_BHF_V\'+name+'_'+COLUMN_NAME+'_log.txt"' 
table_name,name,COLUMN_NAME from tbl_column_length C left join sysobjects  S on C.TABLE_NAME=S.name
where type='u' 
and length <100
and name in('table1')

Original result:

bcp "select id from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1_id.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_id_log.txt"

bcp "select name from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1_name.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_name_log.txt"

bcp "select Dept from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1_Dept.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_Dept_log.txt"

Needed result:

bcp "select id,name,Dept from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_log.txt"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

select 
'bcp "select '+STRING_AGG(COLUMN_NAME,',')+' from [db].dbo.'+name+'" queryout "G:\Test-Bak\Data\db'+'_Load\'+name+'.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\'+name+'_log.txt"' 
table_name--,name,COLUMN_NAME
from tbl_column_length C left join sysobjects  S on C.TABLE_NAME=S.name
where type='u' 
and length <100
and name in('table1')
group by name

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Saran Right there with Babe Points: 753 More actions · Answer 2

Thank you @sean Pearce.

I am doing same for other load but getting an error.

-- Needed Result

LOAD DATA INFILE 'db/table1.txt' INTO TABLE table1 (id,name,Dept)

select 'LOAD DATA INFILE ''dbPainTrax_BHF_V_Load/'+name+'.txt'' INTO TABLE '+name+''+STRING_AGG(COLUMN_NAME,',') ''
table_name--,name,COLUMN_NAME 
from tbl_column_length C left join sysobjects  S on C.TABLE_NAME=S.name
where type='u' 
and length <100
and name in('table1')
group by name

-- Needed Result
LOAD DATA INFILE 'db/table1.txt' INTO TABLE table1 (id,name,Dept)

Saran Right there with Babe Points: 753 More actions · Answer 3

Got it.

select 'LOAD DATA INFILE ''db/'+name+'.txt'' INTO TABLE '+name+'('+STRING_AGG(COLUMN_NAME,',')+');'

from tbl_column_length C left join sysobjects  S on C.TABLE_NAME=S.name
where type='u' 
and length <100
and name in('table1')
group by name

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 4

Saran wrote:

Needed result: bcp "select id,name,Dept from [db].dbo.table1" queryout "G:\Test-Bak\Data\db_Load\table1.txt" -S"server" -U"sa" -P"password" -n -c -t^| -T -o"G:\Test-Bak\Log\db\table1_log.txt"

Lordy... and people wonder why their systems get hacked. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18313 More actions · Answer 5

Saran wrote:

Got it.

select 'LOAD DATA INFILE ''db/'+name+'.txt'' INTO TABLE '+name+'('+STRING_AGG(COLUMN_NAME,',')+');'

from tbl_column_length C left join sysobjects  S on C.TABLE_NAME=S.name
where type='u' 
and length <100
and name in('table1')
group by name

That SQL is awfully written.

Why is there a LEFT JOIN that has a WHERE on the columns of the table (this makes it an INNER JOIN)?

Why have you gone to the effort of creating table aliases then not using the when referencing the columns?

Why don't you use INFORMATION_SCHEMA.COLUMNS view instead?