Problem converting access queries to sql

[DWdrummer]

I'm on the process of migrating in SQL server. We have an access mdb.
i have this table in access and i want to divide this into two separte table. i dont know how to convert this into t-sql code to divide this table into two.

the format of the old table is:
Table1
id, name, desc mat1id, per1, note1, mat2id, per2, note2 .... 20
1 a adesc mat1 1 txt mat2 2
2 b bdesc mat2 2 mat3 4

--- New table
Ntable1
id, name, desc
1, a adesc
2 b bdesc

Ntable2
id, material, percent, note
1, mat1 1 txt
1, mat2 2
2 mat2 2
2 mat3 4

i dont have problem on creating table1.


i have this code in access for table2. but don't know how to convert this in tsql

Do Until rst.EOF
i = 20
For i = 1 To 20
Docmd.Runsql ("INSERT INTO table2_NEW ( id, material, percent, note) & _
"SELECT id, matl" & i & "ID, per" & i & ", note" & i & " & _
"From table1 " & _
"Where id = '" & rst & "' And Not IsNull(mat" & i & "id)")
Next i
rst.MoveNext
Loop



any idea how to convert this in to tsql.

appreaciate any help.

[jagged]

Quick and dirty, something like this:

DECLARE @sql nvarchar(1000)
DECLARE @i int
DECLARE @strI varchar(2)
 
DECLARE @rst nvarchar(100)
 
 
DECLARE curRst CURSOR FOR
SELECT * FROM whatever_rst_is INTO @rst
 
-- Not sure anymore what rst (with no properties/methods) equals in access
 
OPEN curRst;
 
FETCH NEXT FROM curRst;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @i = 1    
    WHILE (@i <= 20)
        BEGIN
            SET @i = @i + 1
            SET @strI = CONVERT(varchar, @i)
 
            SET @sql = '
            INSERT INTO table2_new (id, material, percent, note)
            SELECT id, matl' + @strI + 'ID, per' + @strI + ', note' + @strI + '
            FROM table1
            WHERE id = ' + @rst + ' 
 
            EXEC (@sql)
 
        END
 
    FETCH NEXT FROM curRst INTO @rst
END
CLOSE curRst
DEALLOCATE curRst    
 

Not sure about this part in your code though: "Where id = '" & rst & "' And Not IsNull(mat" & i & "id)")

Since the variable i always has a value, why would mat" & i & "id ever be null?