Creating a table w/ values from another

[OasisChurch]

Hi all,
I am trying to insert data into a table from another table
Do I need to use substring w/ cursors for this?
EX:

table1: ------transform----------> table2:
pin seqno descr pin descr
101-101 1 text 101-101 text
101-102 1 text 101-102 text;text
101-102 2 text 101-103 text
101-103 1 text

My descr field is 60 char long
thanks in advance!!
--scotta

 

[swampBoogie]

It can be done without a cursor if you create a userdefined function that does the concatenation. The UDF needs an implicit cursor though.

Why are you doing this move? To me it looks you are denormalizing data.

 

[OasisChurch]

This move is a one time insert so that our software can read the database properly

thanks,
--scotta

 

[swampBoogie]

You can create an UDF like this

create function swamp(@pin varchar(20)) -- 20?
returns varchar(200)
as
declare @s varcahr(200)
select @s = coalesce(@s+';','') + descr from dbo..table1
where pin = @pin
order by seqno

return @s

and then use it in your insert as

insert into table2 select pin,swamp(pin) from table1
where seqno = 1

Userdefined functions was introduced in SQL server 2000 so if you are at a lower version you need to use the cursor approach.

 

[OasisChurch]

Thanks for the input, however, when I try and run your function create script (mofified of course)I get an "Incorrect syntax near the keyword 'declare'." error
I am running SQLServer2000 w/SP3.

thanks for all your help!!
--scotta

 

[SQLSister]

if you used his exactly as typed, he misspelled varchar in the declare line.

 

[swampBoogie]

Forgot about begin end

create function swamp(@pin varchar(20)) -- 20?
returns varchar(200)
as
begin
declare @s varcahr(200)
select @s = coalesce(@s+';','') + descr from dbo..table1
where pin = @pin
order by seqno

return @s
end

 

[TheBugSlayer]

If T2 exists do:
INSERT INTO T2 SELECT * FROM T1
or in your case:
INSERT INTO T2 SELECT F1 = T1.F1, F3 = T1.F3 FROM T1
this way you get to chose the fields you want to insert.

If you want to create data from another table then do:
SELECT * INTO T2 FROM T1
or in your case:
SELECT F1, F3 INTO T2 FROM T1
or more precisely:
SELECT Pin, Descr FROM Table1 INTO Table2.

This will create a new table Table2 with fields Pin and Descr from Table1.

Hope this helps...

 

[OasisChurch]

Appreciate you all help

The Function was created successfully,
however the insert statement gives me this Error:

"'swamp' is not a recognized function name."

I looked in the sp_help and i can see it but it just does not want to reconize it. Any ideas?

thanks,
--scotta

 

[swampBoogie]

Maybe it is some problem with qualification

if you use

dbo..swamp (pin)

instead? My knowledge of functions in sql server is mostly theoretical.

 

[tlbroadbent]

Use the two part name - dbo.swamp. Scalar functions always require a two part name. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.