How to loop through a table to copy data into another table 1

[dragony]

Hello everyone,

I'm not really a programmer, but need help writting stored procedure that will copy data from an source table (a result of importing data from a text file using DTS) to a destination table with more columns and some data transformation along the way. Here are the table structures:
DESTINATION table
- Seq (int)
- CommDate (smalldatetime)
- Position (char)
- EmpID (char)
- Datasource (char)
- Sales (int)
- Workday (int)
- TrxnStamp (smalldatetime)

SOURCE table
- CommDate (char)
- Position (char)
- EmpID (char)
- Sales (int)
- Workday (int)

I have to loop through the entire SOURCE table and take each record and insert it into the DESTINATION table. Along the way, I have to convert (string manipulations and arithmatic operations) the CommDate (char) into smalldatetime, add the other fields.
There is no field in the SOURCE table that I can use as a counter so how do I loop through it?

Thank you very much in advance,
dragony

 

[tb]

Maybe this will help...

Insert Into "Destination Table"
(DField1, DField2, DField3)
Select SField1, SField2, CAST(SField3 AS CHAR(1))
From "Source Table"
Where "Whatever is neccessary"



I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[SQLSister]

dragony, you should never loop through a table to insert or update records in another table.

tb is on the right track but may not have enough to completely solve your problem. You probably need to use the IsDate function to determine if the info in your character field is a date. You may also need to use CASE to tell it what to do if it is not a date. You can also put calculations in your select for calculated fields. I would set a default value for the TrxnStamp field of getdate(). Then you don't need to ever worry about putting it in the insert. I assume your seq field is an identity? If not it probably should be.

So a sample insert might be:

Insert into DESTINATION (CommDate, Position, EmpID, Datasource, Sales, Workday)
Select  ConvertedDate = Case (IsDate(Comdate)When 1 then Cast(CommDate as smalldatetime) Else Null End) Position, EmpID, 'Destination Table', Sales, Workday from Source

This assumes you don't need to enter Seq as it is an identity or TrxnStamp because it has a default value.

 

[almoes]

hi!

and what if the tables are in different dbs? thanxs.

cheers,
alej

 

[SQLSister]

Depends on whether the dbs are onthe same server or not.

On the same server just reference with the three part name (Database.Owner.Table)

On differnt servers, you will have to set up linked servers (see book Online for instructions) and then use the four part name (Server.Database.Owner.Table)

 

[almoes]

i was missing the owner :-[
thanxs!

 

[nimarii]

Hi - I need to create an insert statement, using both the VALUES & SELECT construct, as there are multiple values coming from different data sources.

Is this possible? I can't seem to figure out how to create an insert statement where I use the SELECT construct, but also insert independent data.

 

[SQLSister]

Usually it's best to start your own thread.

Insert into table1 (field1, field2, field3)
Select table2.testfield, 'hello', Table3.anotherfield from
Table2 join table2 on table2.idfield = table3.SameIDField

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