How to add a new column Dynamically in sql table.

Posted by Sathiya_Narayanan under Sql Server on 10/26/2012 | Points: 10 | Views : 3548 | Status : [Member] | Replies : 2
Hi,

How to added a column dynamically suppose if tabel_1 has 5 columns & another table table_2 has only 3 columns
when copying data from table_1 to table_2 throws error as

"The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."

query
insert into ALTER2..table_2(X,Y,Z,xx)
SELECT * from table_1 where age='10''

for this with out declaring same no of columns as in table_1, when insertion it should dynamically check & add required no of columns according to table_1 & data type is it should as nvarchar(max) for newly added columns in table_2.

Thanks

SATHIYANARAYANAN


Responses

Posted by: Vasanthmvp on: 10/30/2012 [Member] Starter | Points: 25

Up
0
Down
Hey hi,
basically you want to copy data from a table1 with 5 columns to a table2 with 3 columns. You can write a stored procedure instead.
By declaring 3 variables to hold the 3 selected column values of table1.
Insert these variable values into table2.
To copy the whole table, set this select and insert query block under a loop.
If this is not appropriate, let me know.

Regards,

Awesome Coding !! :)

Sathiya_Narayanan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sathiya_Narayanan on: 10/30/2012 [Member] Starter | Points: 25

Up
0
Down
Hi
Have to insert all the 5 column values to table_2.

SATHIYANARAYANAN

Sathiya_Narayanan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response