Multiple values in insert into VALUES(var,var)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple values in insert into VALUES(var,var)

Author

Topic

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-07 : 09:58:47

Hi,
here is something new. Can I use a variable (ex: A) with multiple values in the sentence: insert into "database" (row1, row2...) VALUES(var,var1...)

***********************Here is the code*****************************
Sub test_database_export()
Dim N As Integer
Dim connstring As String
Dim sqlstring As String
Dim data As Variant
Dim data1 As Variant
Dim data2 As Variant
Dim data3 As Variant
Dim data4 As Variant
Dim data5 As Variant
Dim data6 As Variant

N = 16

Do Until ActiveSheet.Cells(N, 8) = ""
N = N + 1
Loop

N = N - 1

data = Range("B16:B" & N & "").Value
data1 = Range("C16:C" & N & "").Value
data2 = Range("D16:D" & N & "").Value
data3 = Range("E16:E" & N & "").Value
data4 = Range("F16:F" & N & "").Value
data5 = Range("G16:G" & N & "").Value
data6 = Range("H16:H" & N & "").Value

sqlstring = "INSERT INTO XXX (Nom, [In], Out_Diner, In_Diner, [Out], Temps_Reel, Temps_arrondi) VALUES ('" & data & "','" & data1 & "','" & data2 & "','" & data3 & "','" & data4 & "','" & data5 & "','" & data6 & "',)"
connstring = "ODBC;DSN=XXX;UID=XXX;PWD=XXX;Database=XXX"

End Sub

//written in VBA\

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-07 : 10:56:55

VALUES will only work with 1 row. You could build UNION ALL statments:

insert into XXX (<col list>)
Select <val list1> UNION ALL
Select <val list2> UNION ALL
Select <val list3>

OR you could create a stored procedure, pass each row as a csv list and let the proc do the inserts.

Be One with the Optimizer
TG

scrap
Yak Posting Veteran

57 Posts

Posted - 2005-03-07 : 11:18:39

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources