Inserting Data dynamically

Question

Inserting Data dynamically

anbillava

Ten Centuries

Points: 1146
More actions
January 13, 2009 at 4:13 pm

#129460

Hello,
I wanted to know is there any way to get column names dynamically
while insertnig data to a table while specifiying column list.
for e.g Table1 has column A and B
while inserting data we do insert into table1(a,b) values('xxxx','yyy');
Here you can see a,b is explicity written. But i wanted to know is there
any way to dynamically specifiy it like getAllColumnNames ...
🙂
Anand

Viewing 8 posts - 1 through 7 (of 7 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Well, you can use Dynamic SQL, but it's a possible security risk.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

anbillava Ten Centuries Points: 1146 More actions · Answer 2

I wrote a scalar function get column names dynamically with column seperated...

But i dint understand about security risk ?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Getting the column names is easy, no dynamic SQL required:

Select *

From INFORMATION_SCHEMA.COLUMNS

Where TABLE_NAME = 'YourTable'

But if you want to use the column names dynamically in an INSERT statement, that will take Dynamic SQL, and the risk is called "SQL Injection" and it comes from executing text supplied by users (or client programs) as Dynamic SQL.

Since you have not really explained what you are doing, I cannot eliminate that as a possibility.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

anbillava Ten Centuries Points: 1146 More actions · Answer 4

I got it. you are talking about string characters (escape) etc. It does not depend on the

user input.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

OK, glad I could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

anbillava Ten Centuries Points: 1146 More actions · Answer 6

Hey Please tel me more about security risks in sql server like sql injection. I dont much

about all this.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Here's a good article on SQL Injection by one of my favorite authors, Michael Coles: http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]