By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,750 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,750 IT Pros & Developers. It's quick & easy.

Problem converting access queries to sql

P: 1
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.
Feb 20 '08 #1
Share this Question
Share on Google+
1 Reply


P: 23
Quick and dirty, something like this:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @sql nvarchar(1000)
  2. DECLARE @i int
  3. DECLARE @strI varchar(2)
  4.  
  5. DECLARE @rst nvarchar(100)
  6.  
  7.  
  8. DECLARE curRst CURSOR FOR
  9. SELECT * FROM whatever_rst_is INTO @rst
  10.  
  11. -- Not sure anymore what rst (with no properties/methods) equals in access
  12.  
  13. OPEN curRst;
  14.  
  15. FETCH NEXT FROM curRst;
  16. WHILE @@FETCH_STATUS = 0
  17. BEGIN
  18.     SET @i = 1    
  19.     WHILE (@i <= 20)
  20.         BEGIN
  21.             SET @i = @i + 1
  22.             SET @strI = CONVERT(varchar, @i)
  23.  
  24.             SET @sql = '
  25.             INSERT INTO table2_new (id, material, percent, note)
  26.             SELECT id, matl' + @strI + 'ID, per' + @strI + ', note' + @strI + '
  27.             FROM table1
  28.             WHERE id = ' + @rst + ' 
  29.  
  30.             EXEC (@sql)
  31.  
  32.         END
  33.  
  34.     FETCH NEXT FROM curRst INTO @rst
  35. END
  36. CLOSE curRst
  37. DEALLOCATE curRst    
  38.  
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?
Feb 22 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.