INSERT VALUES easy question

INSERT VALUES easy question

Post by d » Sat, 03 Aug 2002 01:58:56



Having a mind blank

I want to insert multiple rows of data to a table eg.

TABLE1 (c1 (foriegn key),c2 (foreign key)) PRimary key on c1,c2

say the values I want to insert are as follows

c1 c2
1  1
1  2
1  3

all values exist in the related tables and the primary key constraint
will not be broken.

Can I use the "INSERT INTO VALUES" statement to do it all at once as I
can only seem to do it row by row, from the documentation it seems I
can do like this but I get an error

INSERT INTO TABLE1(c1,c2) VALUES ({1,1},(1,2},(1,3})

or is there a better way of doing it?

 
 
 

INSERT VALUES easy question

Post by John Gilso » Sat, 03 Aug 2002 05:26:43


In SQL-92, you can specify a table constructor using VALUES, e.g.,

CREATE TABLE t1
(
c1 INT NOT NULL,
c2 INT NOT NULL
)

INSERT INTO t1
VALUES (1, 2), (2, 3), (3, 4)

to insert 3 rows in table t1

c1     c2
1       2
2       3
3       4

As far as I know, SQL Server 2000 doesn't implement this feature so
I suppose you'll have to insert row by row, assuming INSERT INTO/SELECT
won't do the trick.

jag


Quote:> Having a mind blank

> I want to insert multiple rows of data to a table eg.

> TABLE1 (c1 (foriegn key),c2 (foreign key)) PRimary key on c1,c2

> say the values I want to insert are as follows

> c1 c2
> 1  1
> 1  2
> 1  3

> all values exist in the related tables and the primary key constraint
> will not be broken.

> Can I use the "INSERT INTO VALUES" statement to do it all at once as I
> can only seem to do it row by row, from the documentation it seems I
> can do like this but I get an error

> INSERT INTO TABLE1(c1,c2) VALUES ({1,1},(1,2},(1,3})

> or is there a better way of doing it?


 
 
 

INSERT VALUES easy question

Post by Erland Sommarsko » Sat, 03 Aug 2002 07:18:24


[posted and mailed, please reply in news]


> Can I use the "INSERT INTO VALUES" statement to do it all at once as I
> can only seem to do it row by row, from the documentation it seems I
> can do like this but I get an error

> INSERT INTO TABLE1(c1,c2) VALUES ({1,1},(1,2},(1,3})

> or is there a better way of doing it?

INSERT TABLE (c1, c2)
    SELECT 1, 1
    UNION
    SELECT 1, 2
    UNION
    SELECT 1, 3

--
Erland Sommarskog, SQL Server MVP

Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

INSERT VALUES easy question

Post by BP Margoli » Sat, 03 Aug 2002 07:25:38


Don,

INSERT INTO TABLE1(c1,c2)
SELECT 1,1
  UNION ALL
SELECT 1,2
  UNION ALL
SELECT 1,3

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Having a mind blank

> I want to insert multiple rows of data to a table eg.

> TABLE1 (c1 (foriegn key),c2 (foreign key)) PRimary key on c1,c2

> say the values I want to insert are as follows

> c1 c2
> 1  1
> 1  2
> 1  3

> all values exist in the related tables and the primary key constraint
> will not be broken.

> Can I use the "INSERT INTO VALUES" statement to do it all at once as I
> can only seem to do it row by row, from the documentation it seems I
> can do like this but I get an error

> INSERT INTO TABLE1(c1,c2) VALUES ({1,1},(1,2},(1,3})

> or is there a better way of doing it?

 
 
 

INSERT VALUES easy question

Post by John Gilso » Sat, 03 Aug 2002 07:22:35


Good point.  Actually, since he said his rows are unique,
one can use UNION ALL.

jag


> [posted and mailed, please reply in news]


> > Can I use the "INSERT INTO VALUES" statement to do it all at once as I
> > can only seem to do it row by row, from the documentation it seems I
> > can do like this but I get an error

> > INSERT INTO TABLE1(c1,c2) VALUES ({1,1},(1,2},(1,3})

> > or is there a better way of doing it?

> INSERT TABLE (c1, c2)
>     SELECT 1, 1
>     UNION
>     SELECT 1, 2
>     UNION
>     SELECT 1, 3

> --
> Erland Sommarskog, SQL Server MVP

> Books Online (updated!) for SQL 2000 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
 
 

1. inserting numeric value, API insert method question

Hello again,

I am having an issue inserting numeric values into my database. I have
the ddf available, telling me that the data type is COBOL Decimal
comp-3. It has a length of 8 with 2 decimals.

When I push in integers, it is ok. I tried to push it as a double, then
tried it as a variant with a decimal sub-type. it Still doen not work.

Do I need to do some low-level conversion of numbers in order to push
them into comp-3 typed fields?

This type was indicated by DDF Periscope. I tried to view the structure
of my btr file directly, the same fields were shown as being floats.

Any ideas?

Thx Cheers, Laurent

--
Posted via dBforums
http://dbforums.com

2. Cancelling and SQL statement. Should be simple???

3. Prolly easy question: putting values in listbox

4. Informix - using HP AutoRAID

5. Insert Query Help!!!!!!!!EASY Question

6. how to do next?

7. Give this newbe easy answers on easy questions!

8. IDS 9.30 with redhat linux 7.3 problem.

9. Newbie Insert Value question

10. Newbie Question: Finding the just-inserted-value of a field

11. inserting all child values into temp table (XPath question)

12. Question about insert and defaut values

13. SQL Question: INSERT with SELECT in VALUES section