use column names and not positions when inserting from one table into another

[Tyler Hudson]

/*Code below raises following errors:
Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value 'a' to a column of data type
int.
*/

create table #x (i integer, c char(1))
create table #y (c char(1), i integer)

insert into #x VALUES (1, 'a')

insert into #y SELECT * from #x
drop table #x
drop table #y
--CODE COMPLETE

Is there a way to use the column names as the basis for the insert as
opposed to column position? The key here is that I DO NOT want to
SPECIFY COLUMN NAMES (i.e. 'insert into #y (i,c) SELECT * from #x')
Regards,

Ty

[David Portas]

You could build the INSERT statement programmatically as a string and then
execute it in an EXEC statement but I wouldn't really recommend it. I
suggest you *always* specify the column names in an INSERT statement and
don't use SELECT * in production code - in the long run it makes your code
much easier to maintain.

--
David Portas
SQL Server MVP
--

[--CELKO--]

>> Is there a way to use the column names as the basis for the insert
as opposed to column position? <<

You did not bother to look at the syntax of an INSERT INTO statement.
You just put a list of columns after the table name. Those columns
which are not listed will be given their default values. This is the
way you should have been writing production code all along.

[Erland Sommarskog]

Tyler Hudson (ty****@allpax.com) writes:

/*Code below raises following errors:
Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value 'a' to a column of data type
int.
*/

create table #x (i integer, c char(1))
create table #y (c char(1), i integer)

insert into #x VALUES (1, 'a')

insert into #y SELECT * from #x
drop table #x
drop table #y
--CODE COMPLETE

Is there a way to use the column names as the basis for the insert as
opposed to column position? The key here is that I DO NOT want to
SPECIFY COLUMN NAMES (i.e. 'insert into #y (i,c) SELECT * from #x')

No.

And in my opinion it is bad coding practice to use SELECT * and INSERT
without a column list. It's OK to do if the table in question is a
temp table created in the same stored procedure, but for a permanent
table it is a no-no.

The reason is that if the DBA makes what appears to be a harmless change
sucb as adding a nullable column, he will break your code if you say
things like:

INSERT tbl
SELECT a, b, c FROM othertbl

If you specify the column you are inserting into, your code is safe.

Likewise, using SELECT * can cause nasty surprises.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp