Sql-server 2003, Confusion On The 'insert...select' Functionality

[benbotektips]

I have some files on the AS400 to be copied into SQL-Server, for further massage/ETL. I'm copying an existing DTS package, and everything works except adding fields onto an existing table. I think I'm misunderstanding the 'INSERT...SELECT' functionality.
In the AS400 files, there's a date field 'sdate' defined as decimal, precision 6, scale 0 - meaning June 20 2005, would be '62005'. I need to add a field with the reformatted date '06/20/2005'. The existing DTS package I'm copying from has the SQL something like this (exact SQL unavailable, server is down), where 'saledate' is the newly formatted field:

insert SALES
select *,
saledate = (case when sdate = 0 then 0 else
cast
(stuff(stuff(sdate + 1000000, 7) 3, 0, '/',) 6,0,'/')
as smalldatetime
)
from AS400a.saleslib.salesdata.allsales

The sytax is probably off a little, but even if so, the point is, it looks like the SQL is doing 3 things at once; selecting all fields from the original file to copy to the table, defining a new field for the table, and filling it with the data as formatted.
When I try to copy this for another table, I get an error message back telling me there's no such column, or column not found. This leads me to think the 'INSERT...SELECT' can't both define a new column (like 'ALTER TABLE' does), AND fill it with data. Yet, in the tables from the original DTS package, I don't see any other place where new columns are defined, like with 'ALTER TABLE'. In the AS400 library & the SQL-server staging database, there's only the original date field, 'sdate'; in the summary (final) database, SQL-Server table 'SALES' has the field 'saledate', in the format 'mm/dd/yyyy', with the '/'.
So, can the 'INSERT...SELECT' statement select all fields from an existing AS400 file, define a new column, AND fill it up with reformatted data,
OR,
is it necessary to 1st create the table in SQL-Server with all the original fields, ALTER TABLE to add the new column 'saledate smalldatetime', (which must have been done manually when the original table was 1st created on SQL-Server), then run the 'INSERT...SELECT'?

 

[Merkaba]

try making a SP to do it like this:

CREATE PROC MakeSALESTable
AS
DECLARE @TempTable TABLE
(
--Make a dup of SALES plus an ID
TempID int IDENTITY(1,1),
SomeColumn varchar(20),
sdate varchar,
SalesDate varchar
)
--Declare Variables for each Column in the temp Table
DECLARE @VarID int
DECLARE @VarCol varchar
DECLARE @VarSDate varchar
DECLARE @VarSalesDate varchar

INSERT INTO @TempTable (SomeColumn, SDate) 
SELECT * FROM AS400a.saleslib.salesdata.allsales 

DECLARE TempCursor CURSOR FOR
SELECT * FROM @TempTable

OPEN TempCursor

FETCH NEXT FROM TempCursor
--Use as many variables as there are
--columns in the TempTable
INTO @VarID, @VarCol, @VarSDate, @VarSalesDate

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @VarSDate = Null 
	BEGIN
		UPDATE @TempTable SET
		SalesDate = 0
		--set 0 equal to your needs	 
		WHERE tempID = @VarID
	END
	FETCH NEXT FROM TempCursor
	--Use as many variables as there are
	--columns in the TempTable
	INTO @VarID, @VarCol, @VarSDate, @VarSalesDate

END

INSERT INTO SALES SELECT SomeColumn, SDate, SalesDate FROM @TempTable

GO

Try that out


-Bob

 

[benbotektips]

Thanx.
This is very informative for scripting, and I have saved it for future need. It's ambitious for what I want to do now. I can always just do the 'ALTER TABLE' once, in Query Analyzer, for each table I want to have the reformatted new date fields in.
The basic question is, does 'INSERT...SELECT' allow you to simultaneously
a) define a new field for your (existing) target table, while
b) selecting all (*) from the source table, and
c) filling the new field with data, based on one of the fields pulled in with b)?
OR
is the select filling data into a table that had the field created already (with 'ALTER TABLE)?

 

[Merkaba]

yeah, i tried to use the INSERT INTO ... SELECT and I couldn't get it working like you need. It seems you have to ALTER TABLE after populating the new table...

Correct me if I'm wrong.. I'm interested to see if you can do this an INSERT INTO ... SELECT, too.


-Bob

 

[SQLSister]

Insert into will create a new table. I personally never, ever use it. I create a table with the exact structure I need and then insert the data using an ordinary insert.

It appears to me that the reason why it didn;t work when you tried in on a different table was that the table structure was not appropriate. It proably wasn;t defining a new field for the orginal table but just creating the data for that field. But if you tried to then use it where the field did not already exist, of course the statment would fail. This is one reason why I alwasy define my table first and then always use the exact column names in bothe the insert and the select part of the statment.

It is an extremely poor practice to ever use select * as part of an insert statment. You should always make sure you are inserting the columns correctly. For instance suppose the sales table has the columns, salesID, SAlesDAte, Saleman. If you select from a diffent table which has SalesID, Salesman, Salesdate, then the data will try to go into the wrong columns. If the other table has more or less fields, then the statment won't work either as the select must have the exact same number of columns as the insert.

If someone adds a column to the Sales table but no to the table where you are inserting the data from, a statment that worked fone for months or even years will suddenly break even though the new column is not a required column.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[amrita418]

If you are fine with adding varchar field to the new table instead of smalldatetime then you can create the table with data in one stmt like this:

select 		*, 
		case when sdate = 0 then '01/01/1900'    else '01/01/2001' end SaleDate 
into 		sales 
from 		AS400a.saleslib.salesdata.allsales

Regards,
AA

 

[benbotektips]

We're getting warmer.
I agree with SQLsister about the bad idea of 'select *', however, I need a quick and dirty way to add on one field to a long production table, that already exists. Since I own the step that will update, I can recreate it as I want.
What Amrita's saying, is I can create a field in a new table. Problem is, the tables are created without the reformatted date in a production package, which I can't touch. Boss's orders.
So, in general, to create a new field on an existing table, AND fill it with data, there's no way to do it in one step, the column must be created (via ALTER TABLE), then loaded?
No shortcuts?