How to add new column to an existing table while inserting values

Question

How to add new column to an existing table while inserting values

srikale

SSC Enthusiast

Points: 165
More actions
June 11, 2009 at 10:21 pm

#131754

Hi ,
I want to insert a new column , check while inserting values.
For example : I have a table TBL with 2 columns , but i want to insert 3 columns so i need to write code that checks wether 3rd column is existing or not and then add new cloumn.But not using simple alter table.
But i need code to compare the values (columns ) while passing in insert statement with columns in That Table.If that column doesnot exits it should automatically add that column.
And i need this code in Dynamic TSQL

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

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Wow.

I suspect that you are getting a comma-separated list of values, and you don't know how many columns you will need to store the data until you get the list?

Anyway, you might be better thinking of storing them as related rows, rather than columns. Then you can use a trick similar to the following:

with

ExampleData as (

select '1,2,3' as ListOfValues union all

select '5,6,7,8' as ListOfValues union all

select '9,10' as ListOfValues

)

select*

fromExampleData

cross apply

dbo.fnSplit(ExampleData.ListOfValues, ',')

You can find the code for fnSplit somewhere around on this site. 🙂

[/font]

SqlUser-529296 SSCarpal Tunnel Points: 4072 More actions · Answer 2

I hope this may solve your issue.

IF EXISTS(SELECT * FROM sys.objects WHERE [name] = 'tblTable')

BEGIN

DROP TABLE tblTable

END

CREATE TABLE tblTable (fldID int, fldName varchar(8))

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'tblTable' AND COLUMN_NAME LIKE 'fldDesc')

BEGIN

ALTER TABLE tblTable ADD fldDesc varchar(8)

END

INSERT INTO tblTable VALUES (1,'MyName1','MyDesc1')

INSERT INTO tblTable VALUES (2,'MyName2','MyDesc2')

SELECT * FROM tblTable

FreeHansje SSChampion Points: 11751 More actions · Answer 3

He said something about not using ALTER...:-P

Greetz,
Hans Brouwer

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

srikale (6/11/2009)
Hi ,
I want to insert a new column , check while inserting values.
For example : I have a table TBL with 2 columns , but i want to insert 3 columns so i need to write code that checks wether 3rd column is existing or not and then add new cloumn.But not using simple alter table.
But i need code to compare the values (columns ) while passing in insert statement with columns in That Table.If that column doesnot exits it should automatically add that column.
And i need this code in Dynamic TSQL

I know it's what you want to do, but it may not be the right thing to do. Perhaps if you told us what you're really trying to accomplish, we could provide a better solution.

--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)
Intro to Tally Tables and Functions

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 5

srikale (6/11/2009)
But not using simple alter table.

What's the problem with ALTER?

:ermm:

Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems