Select from one table to another and changing data type - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Select from one table to another and changing data type

Select from one table to another and changing data type

Options

Previous Topic · Next Topic

pdbowling

Posted: Thursday, July 24, 2008 10:36:39 AM

Rank: Newbie

Joined: 1/18/2008
Posts: 2
Points: 6
Location: Lexington, KY

Hi, everyone.

I've got a table with some integer values I need to move to another table on a different sql server.

So, getting the data is easy.

Code:

SELECT fieldName from tableName.

This is an int field.

I have a table formatted differently on another sql server that I need to move this data to. I was considering using an INTO statement but have another concern.

I should get back from the SELECT something like

Code:

1
2
11
25
101
999

I need to move this data into a char(3) field

Code:

newTable
ID............int (autonumber)
myValue.......char(3)
desc..........varchar(20)

The values need to left fill with zeros if they are not 3 characters long (no idea here).

Code:

001
002
011
025
101
999

I'm OK on the select but I get twisted around on the syntax for selecting Into because there are more than one columns in the target table and I am not sure how to designate the target field in the query. Also, I am uncertain how to transform 1 into 001 etc before insert.

Would anyone have some suggestions they would be willing to share?

Thanks
Patrick

Scott Whigham

Posted: Thursday, July 24, 2008 1:35:01 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hi patrick -

Here's the code to do what you want:

Code:

declare @Source table (ID int not null identity(1,1))
declare @rows int, @i Int
set @rows = 110
Set @i = 0
while @i < @rows
  begin
      insert @source default values
      set @i = @i + 1
  end

select id
  , case
      when id < 100 then case
          when id < 10 then '00' + cast(id as varchar)
          else '0' + cast(id as varchar)
      end
      else cast(id as varchar)
  end as formatted
from @source
where id<1000

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.