|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
INSERT INTOIf I have 4 values 1, 2, 3, and 4 that I want to insert into TableA with FieldA how can this be done with the basic INSERT INTO syntax? INSERT INTO target [(field1[, field2[, ...]])] VALUES (value1[, value2[, ...]) you could add a single value with: INSERT INTO TableA (FieldA) VALUES (1) ....but how can this be managed with a single SQL statement, INSERT or otherwise? Thanks again. Alastair MacFarlane INSERT INTO inserts a single record in the database and what you seem to
want it adding four records with one SQL Statement. Either pass 4 SQL statements or open a recordset for editing and add the 4 records that way... -- Show quoteChris Hanscom - Microsoft MVP (VB) Veign's Resource Center http://www.veign.com/vrc_main.asp Veign's Blog http://www.veign.com/blog -- "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message news:%23b2HeHO1FHA.3660@TK2MSFTNGP15.phx.gbl... > Dear All, > > If I have 4 values 1, 2, 3, and 4 that I want to insert into TableA with > FieldA how can this be done with the basic INSERT INTO syntax? > > INSERT INTO target [(field1[, field2[, ...]])] > VALUES (value1[, value2[, ...]) > > you could add a single value with: > > INSERT INTO TableA (FieldA) > VALUES (1) > > ...but how can this be managed with a single SQL statement, INSERT or > otherwise? > > Thanks again. > > Alastair MacFarlane > > > > > > > INSERT INTO TableA (FieldA, FieldB)
VALUES (1, 2) For MS SQL Server reference see here(click on "Sync toc" under "Go" button): http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_5cl0.asp Show quote "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message news:%23b2HeHO1FHA.3660@TK2MSFTNGP15.phx.gbl... > Dear All, > > If I have 4 values 1, 2, 3, and 4 that I want to insert into TableA with > FieldA how can this be done with the basic INSERT INTO syntax? > > INSERT INTO target [(field1[, field2[, ...]])] > VALUES (value1[, value2[, ...]) > > you could add a single value with: > > INSERT INTO TableA (FieldA) > VALUES (1) > > ...but how can this be managed with a single SQL statement, INSERT or > otherwise? > > Thanks again. > > Alastair MacFarlane > > > > > > >
Show quote
"Alastair MacFarlane" <anonym***@microsoft.com> wrote in message It can't.news:%23b2HeHO1FHA.3660@TK2MSFTNGP15.phx.gbl... > If I have 4 values 1, 2, 3, and 4 that I want to insert into TableA with > FieldA how can this be done with the basic INSERT INTO syntax? > > INSERT INTO target [(field1[, field2[, ...]])] > VALUES (value1[, value2[, ...]) > > you could add a single value with: > > INSERT INTO TableA (FieldA) > VALUES (1) > > ...but how can this be managed with a single SQL statement, INSERT or > otherwise?
Show quote
"Alastair MacFarlane" <anonym***@microsoft.com> wrote in message This is really more appropriate for a SQL newsgroup than a VB newsgroup. news:%23b2HeHO1FHA.3660@TK2MSFTNGP15.phx.gbl... > Dear All, > > If I have 4 values 1, 2, 3, and 4 that I want to insert into TableA with > FieldA how can this be done with the basic INSERT INTO syntax? > > INSERT INTO target [(field1[, field2[, ...]])] > VALUES (value1[, value2[, ...]) > > you could add a single value with: > > INSERT INTO TableA (FieldA) > VALUES (1) > > ...but how can this be managed with a single SQL statement, INSERT or > otherwise? You're not asking about VB or VB code. You're asking about SQL. But, to answer your question...I'm not sure what you're asking. Are these 4 values 4 different records? Do you want these 4 values inserted as ONE record all into FieldA of TableA? If the former, you'd need 4 INSERT SQL statements. If the latter, you'd need to concatenate the 4 values. Even so, that's assuming that data type for the field is a string type (CHAR or VARCHAR, for example). It's not clear, at least to me, what you're wanting to do. Can you elaborate? -- Mike Microsoft MVP Visual Basic MikeD and others,
Thanks for the replies. I had hoped there was a single SQL statement that could send 4 values to the same field making 4 database records. From the group I now know that you have to send 4 separate SQL statements. I assumed this and I was wondering whether the group could see any alternative. Four SQL statements it will be. Thanks Alastair Show quote "MikeD" <nob***@nowhere.edu> wrote in message news:OjzBR9O1FHA.2616@tk2msftngp13.phx.gbl... > > "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message > news:%23b2HeHO1FHA.3660@TK2MSFTNGP15.phx.gbl... >> Dear All, >> >> If I have 4 values 1, 2, 3, and 4 that I want to insert into TableA with >> FieldA how can this be done with the basic INSERT INTO syntax? >> >> INSERT INTO target [(field1[, field2[, ...]])] >> VALUES (value1[, value2[, ...]) >> >> you could add a single value with: >> >> INSERT INTO TableA (FieldA) >> VALUES (1) >> >> ...but how can this be managed with a single SQL statement, INSERT or >> otherwise? > > > This is really more appropriate for a SQL newsgroup than a VB newsgroup. > You're not asking about VB or VB code. You're asking about SQL. > > But, to answer your question...I'm not sure what you're asking. Are these > 4 values 4 different records? Do you want these 4 values inserted as ONE > record all into FieldA of TableA? If the former, you'd need 4 INSERT SQL > statements. If the latter, you'd need to concatenate the 4 values. Even > so, that's assuming that data type for the field is a string type (CHAR or > VARCHAR, for example). > > It's not clear, at least to me, what you're wanting to do. Can you > elaborate? > > -- > Mike > Microsoft MVP Visual Basic > > > Hi Alastair:
It all depends on where you are getting your values from. If you are getting them from another table, then you could it like this: INSERT INTO MyTable (MyField) SELECT MyField FROM MyOtherTable WHERE MyField IN (1,2,3,4) hth, Doug. Show quote "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message news:ec4HWOP1FHA.2540@TK2MSFTNGP09.phx.gbl... > MikeD and others, > > Thanks for the replies. I had hoped there was a single SQL statement that > could send 4 values to the same field making 4 database records. From the > group I now know that you have to send 4 separate SQL statements. I assumed > this and I was wondering whether the group could see any alternative. Four > SQL statements it will be. > > Thanks > > > Alastair > "MikeD" <nob***@nowhere.edu> wrote in message > news:OjzBR9O1FHA.2616@tk2msftngp13.phx.gbl... > > > > "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message > > news:%23b2HeHO1FHA.3660@TK2MSFTNGP15.phx.gbl... > >> Dear All, > >> > >> If I have 4 values 1, 2, 3, and 4 that I want to insert into TableA with > >> FieldA how can this be done with the basic INSERT INTO syntax? > >> > >> INSERT INTO target [(field1[, field2[, ...]])] > >> VALUES (value1[, value2[, ...]) > >> > >> you could add a single value with: > >> > >> INSERT INTO TableA (FieldA) > >> VALUES (1) > >> > >> ...but how can this be managed with a single SQL statement, INSERT or > >> otherwise? > > > > > > This is really more appropriate for a SQL newsgroup than a VB newsgroup. > > You're not asking about VB or VB code. You're asking about SQL. > > > > But, to answer your question...I'm not sure what you're asking. Are these > > 4 values 4 different records? Do you want these 4 values inserted as ONE > > record all into FieldA of TableA? If the former, you'd need 4 INSERT SQL > > statements. If the latter, you'd need to concatenate the 4 values. Even > > so, that's assuming that data type for the field is a string type (CHAR or > > VARCHAR, for example). > > > > It's not clear, at least to me, what you're wanting to do. Can you > > elaborate? > > > > -- > > Mike > > Microsoft MVP Visual Basic > > > > > > > > "Alastair MacFarlane" <anonym***@microsoft.com> wrote in message Nope. I suppose in some cases it'd be nice to be able to that, but a SQL news:ec4HWOP1FHA.2540@TK2MSFTNGP09.phx.gbl... > MikeD and others, > > Thanks for the replies. I had hoped there was a single SQL statement that > could send 4 values to the same field making 4 database records. From the > group I now know that you have to send 4 separate SQL statements. INSERT statement adds only one record at a time. Chris (Veign) mentioned opening a recordset object. That is another way you could approach this. It would not involve using any SQL at all. Yet, another approach would be a stored procedure to which you pass the 4 values (the SP would do the 4 INSERTs; from your VB app, you'd just call the SP and pass it the 4 values). Given that you never stated what database you're using, it's hard to say how else you could accomplish this (or if even using an SP would be an option). -- Mike Microsoft MVP Visual Basic I'm assuming you're not using a secondary table for your information gathering...
If you're using an SQL server... INSERT INTO TableA SELECT '1st entry' UNION SELECT '2nd entry' UNION SELECT '3rd entry' UNION SELECT '4th entry' if not then create a 'Dummy' table, and insert one AND ONLY one row into it. then INSERT INTO TableA SELECT '1st entry' from Dummy UNION SELECT '2nd entry' from Dummy UNION SELECT '3rd entry' from Dummy UNION SELECT '4th entry' from Dummy hth, D. FWIW, heres the tables I'm using in this example... CREATE TABLE [dbo].[Dummy] ( [DummyCol] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TableA] ( [FieldA] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO One row in Dummy that has a value of 1 for the DummyCol field. It's just there because some of the DB's i work with do not allow working with select statements that have no From clause... On oracle, use DUAL as the table instead of DUMMY.
Show quote
"GrandNagel" <NOTGrandNa***@HotMail.com> wrote in message A guy I work with would call that "underground parking." While it does work news:ubh43qP1FHA.4064@TK2MSFTNGP09.phx.gbl... > I'm assuming you're not using a secondary table for your information > gathering... > > > If you're using an SQL server... > > INSERT INTO TableA > SELECT '1st entry' > UNION > SELECT '2nd entry' > UNION > SELECT '3rd entry' > UNION > SELECT '4th entry' and it is technically a single SQL statement, it supports this scenario only and isn't expandable to more or fewer values. Clever, though. Jeff Johnson [MVP: VB] wrote:
> A guy I work with would call that "underground parking." While it does work It is _actually_ one statement which is what the OP was after, one trip to the> and it is technically a single SQL statement, it supports this scenario only > and isn't expandable to more or fewer values. DB is all that's required. When generating your own SQL on the fly it scales up or down using a loop to build the SQL quite nicely. I offered up the desired solution to his problem within the limits that were requested when no one else had a viable answer... knock it if you will but it works and does what he asked for... can't ask for more than that :-) D.  
Other interesting topics
|
|||||||||||||||||||||||
