Insert query and number of fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert query and number of fields

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-18 : 10:09:44

I've found out how to to the Insert into my table (col1, col2) Select (col1, col2...) from othertable where regId= @regId in my earlier question but do i have to name every column as i have about 80 in my table. Can't I use an asterisk or something....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-18 : 10:15:17

If the table structure changes. using * may lead to error

Make use of the output of this

select column_name+',' from information_schema.columns
where table_name='your_table'

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-18 : 10:40:16

Here's my sp - how do i use the code you have given me in this ? I used one of my columns Reg_Id to make sure it worked but couldn't seem to get the syntax correct using a * instead.

INSERT INTO tblPHCR_Register_Audit (Reg_Id)(SELECT(Reg_Id) FROM tblPHCR_Register WHERE Reg_Id = @RegId)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-19 : 02:44:25

INSERT INTO tblPHCR_Register_Audit (Reg_Id)
SELECT Reg_Id FROM tblPHCR_Register WHERE Reg_Id = @RegId

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-19 : 03:57:18

quote:
Originally posted by Pinto

Here's my sp - how do i use the code you have given me in this ? I used one of my columns Reg_Id to make sure it worked but couldn't seem to get the syntax correct using a * instead.

INSERT INTO tblPHCR_Register_Audit (Reg_Id)(SELECT(Reg_Id) FROM tblPHCR_Register WHERE Reg_Id = @RegId)

if you want to use * use it like this

INSERT INTO tblPHCR_Register_Audit
SELECT * FROM tblPHCR_Register WHERE Reg_Id = @RegId

But as madhi said If the structure of your source table changes, you need to make sure the structure of your table also changes to be consistent with source, else the insert will break.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources