How do I ... loop horizontal?

If I have the raw data dumped into a big table as following:

Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7

And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?

Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6

If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsonsUsing Perl this would be relatively easy (basically a one line script).

Using pure SQL Server tools (BCP and Transact-SQL), it can be done, but it would be rather ugly.

I'd try to request the data in another format. It would be easier if it was provided in a "cleaner" format. If that isn't a choice, look at the tools that you've got to see what makes the most sense, then use that to fix the problem.

-PatP|||I know when I'm looped I'm usually horizontal :D|||you're not drunk if you can lie on the floor without holding on|||shiparsons, please do not post two threads with the same subject on the same forum. If you have additional information, just append it as a new post.

If the number of blocks is constant, you could do this as a single butt-ugly UNION statement.

Otherwise, it is dynamic SQL time for you.

I may have a dynamic SQL algorithm that would be pretty concise for you. I'll try it in the morning if nobody else posts it first...|||You can use a simple for loop in DOS/NT Shell language to parse the file and generate new output to a redirected file if you do not have access to perl or a tool such as MKS Toolkit

It may be a bit of of work since you have multiple rows on a single line.

Google "for loop" "DOS" and you should be able to get some assistance.|||Here is some code that selects N columns at a time from any table.

You can modify it to do inserts, if you would like.

declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int

set @.ColumnIncrement = 3 --Number of columns to return for each statement
set @.TableName = 'TableName' --Name of your target table

set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)

while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement

select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid

exec ('select ' + @.ColumnString + ' from ' + @.TableName)
end|||Thank you for the suggestion and assistance.
I am trying to avoid using other "Langauage/command" and use SQL instead. I belive there should be a way to achieve what I am trying to do within SQL. It may need multiple steps.

I appreciate any brainstorming and help.
shiparsons|||Did you try the code I posted?|||Blindman,
Thank you for the help. I guess while I was composing my respose you just posted the code. Anyway, I will try and definitely let you know.

Many thanks,
shiparsons|||Blindman,
I have tried the code... You are the best! It works exactly what I want!

I tried to modify the code to insert into a table but got error message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test2Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test3Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test4Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test5Date'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test6Date'.
....

Here is the code I modified. Any suggestion? Thank you in advance for the help!!
declare @.ColumnString varchar(500)
declare @.ColumnCounter int
declare @.ColumnIncrement int
declare @.TableName varchar(500)
declare @.NumColumns int
declare @.Staging varchar(500)

set @.ColumnIncrement = 8
set @.TableName = 'TestTable'
set @.Staging= 'Staging'
set @.ColumnCounter = 0
set @.NumColumns =
(select count(*)
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName)

while @.ColumnCounter < @.NumColumns
begin
set @.ColumnString = null
set @.ColumnCounter = @.ColumnCounter + @.ColumnIncrement

select @.ColumnString = isnull(@.ColumnString + ', ', '') + syscolumns.name
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.name = @.TableName
and colid > @.ColumnCounter - @.ColumnIncrement
and colid <= @.ColumnCounter
order by colid

exec ('insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName)

end|||Why are you inserting into your staging table? I though the idea was to transform data while populating production tables?

I think it is erroring out because your dynamic INSERT statement does not list which column(s) to insert into.

Copy this just before your EXEC statement and see what code it is trying to run:

'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName

If your Query Analyzer is set to output Text results then you can just copy the output into another QA window and execute it directly to trace your error.

Error tracing is one of the challenges of dynamic SQL.|||I still need to do some manipulations before load into production table. Thats why I need to insert into staging table.

I tried the code right before EXCE statement and still got error message:

Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'insert into'.

Any idea?

Thanks|||Comment out the EXEC statement so that you don't throw the error anymore, and instead post the dynamic SQL statement that would be executed.|||I replaced Exec (..) with the command :

'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName

And run the entire code. However, I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 32
Line 32: Incorrect syntax near 'insert into'.

What part is incorrect? I could not figure it out.

Thanks for the help again.
shiparsons|||how 'bout:

Declare @.sql varchar(8000), @.staging sysname, @.ColumnString varchar(12), @.TableName sysname, @.debug bit

Select @.staging = '@.t1', @.TableName = '@.t2', @.ColumnString = 'c2', @.debug = 0

Select @.sql = 'Set NoCount On Declare @.t1 table(c1 int) Declare @.t2 table(c2 int)'
Select @.sql = @.sql + char(10) + 'Insert @.t2 (c2) Select 1 Union Select 2'

Select @.sql = @.sql + char(10) + 'insert ' + @.staging + ' select ' + @.ColumnString + ' from ' + @.TableName
Select @.sql = @.sql + char(10) + 'Select * From @.t1; Select * From @.t2'

If @.debug = 1 Select @.sql
Else Exec (@.sql)|||Blindman,
I think I know the problem is...
The entire code you posted which pulls out the results I want and in addition it stacks all the column headings as well. That's why when I insert into 'staging table' error msg shows up.

Here is the example of what I mean:
the results from running the code

date column1 column2... column8
data set...
date column10 column11...column16
data set...
...

I have defined datatype for each column of my staging table as (date datetime and all else is float). That is why 'insert into' caused error. Is there any a way to modify or skip the headers?

Any suggestions are welcome!
Thanks for the help!
shiparsons|||I hope you meant that you replaced it with

SELECT 'insert into' + @.staging + 'select ' + @.ColumnString + ' from ' + @.TableName

It should not give you an error...

...and yes, it is likely that the problem is a lack of specifed columns for your insert statement. If the column names are the same, you should be able to run something like this:

exec ('insert into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)|||Blindman,
I think the code should be:
exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)

And I am still getting the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test1Date'.
...
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Test32Date'.

Unfortunately, all columns names are different ( total 32 which is standard for each input file).

Any other ideas?
Thanks.

MaxA,
I like your code which is simpler, however, like the problem i have with the code Blindman posted. Your code will mix the column headings into the results as well. I need to insert the results into staging table which has each datatype defined as either datetime or float. Is there a way not pulling the column headers as a part of results?

Results from your code
c1
----
1
2

c2
----
1
2

expected results so that I can insert into staging table
c1
----
1
2

1
2

Any help is highly appreciated!
shiparsons|||"exec ('insert' + 'into' + @.staging + '(' + @.ColumnString + ') select ' + @.ColumnString + ' from ' + @.TableName)"?

No, there isn't any need to split 'insert into' into 'insert' + 'into', which won't work anyway because you ommited the space character, and so it concatenates to "insertinto". That is certain to throw a syntax error.

To get around the different column names, try creating a view(s) based upon either your staging table or your production table that aliases the column names so they are compatible. Then reference the view in your statements rather than the table itself.

But it you are trying to insert into your staging table and your staging table is the same poorly designed schema you mentioned in your first few posts, then I think you are screwed because you are going to have a tough time getting your INSERT statement to insert into columns 1-5 on one run, 6-10 on then next, and so on...|||The results you are seeking are unexpected.

Remove ; Select * From @.t2 from my example - it was put there to demonstrate the accuracy of the Select * From @.t1.|||Blindman,
Here is the right code to insert into a table, which works exactly what I want!

insert into tablename
exec ('select ' + @.ColumnString + ' from ' + @.TableName)

Many thanks to everyone who replied this thread!

shiparsons