Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2005
    Posts
    88

    Question Unanswered: 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.
    shiparsons

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using 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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I know when I'm looped I'm usually horizontal
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're not drunk if you can lie on the floor without holding on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.

    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2005
    Posts
    88
    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

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Did you try the code I posted?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2005
    Posts
    88
    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

  11. #11
    Join Date
    Feb 2005
    Posts
    88
    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

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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:

    Code:
    '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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2005
    Posts
    88
    I still need to do some manipulations before load into production table. That’s 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

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2005
    Posts
    88
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •