Copying a long record to another table

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

SQL Server 2000 Forums

SQL Server Development (2000)

Copying a long record to another table

Author

Topic

SQLSlave
Starting Member

8 Posts

Posted - 2006-06-07 : 21:55:13

I am trying to copy a record from one table to another. Both tables have the same record fields. The record in ScheduleDataMaster
has about 30 fields. Do I have to name every field and not use a wildcard ?

Tried this:

select *
INTO [ATMSchedule].[dbo].[ActiveData]
from [ATMSchedule].[dbo].[ScheduleDataMaster]
where SchID = 5107

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 22:01:08

if the ordering of the column is the same, you can do this

insert into ActiveData
select * from ScheduleDataMaster
where SchID = 5107

But it is not advisable to do this. As you might have make changes to the table (new column etc) and the column ordering might change. So it is always better to specify the column name.

insert into ActiveData(col1, col2, col3, ...)
select col1, col2, col3, ...
from ScheduleDataMaster
where SchID = 5107

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-07 : 22:04:50

if you are lazy to type in all the column name

, make use of this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53384

SQLSlave
Starting Member

8 Posts

Posted - 2006-06-07 : 22:09:12

Thank you,

I like to let the computer do all the work :)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-07 : 22:19:25

or...

declare @colname varchar(2000)

select @colname=coalesce(@colname +',','') + (table_name + '.' + column_name) from information_schema.columns where table_name in ('tablename1','tablename2')
order by table_name

select @colname

--------------------
keeping it simple...

SQLSlave
Starting Member

8 Posts

Posted - 2006-06-08 : 01:38:09

That is great!

It's a keeper in my reference files.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources