Menu
I have the following insert statement which works fine on one system running SQL Server 2008 but I've tried the same thing on a system running 2000 and it gives an error...
Is there an easy fix for this since I have over 3000 records to insert and I don't want to have to do them all one at a time!
INSERT INTO uk_postcodes (outcode, lat, lng)
VALUES ('AB12', '57.098381', '-2.172400'),('AB13', '57.108', '-2.237')
585
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
A table can store upto 1000 rows in one insert statement. If a user want to insert multiple rows at a time, the following syntax has to written. If a user wants to insert more than 1000 rows, multiple insert statements, bulk insert or derived table must be used.
The INSERT statement also allows you to insert multiple rows into a table using a single statement as the following: INSERT INTO table_name(column1,column2…) VALUES (value1,value2,…), (value1,value2,…), … In this form, you need to provide multiple lists of values, each list is separated by a comma.
In a multitable insert, you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more tables. Specify ALL followed by multiple insert_into_clauses to perform an unconditional multitable insert.
Using SQL server 2000, there are two ways to do this -
one is
INSERT INTO uk_postcodes (outcode, lat, lng)
VALUES ('AB12', '57.098381', '-2.172400');
INSERT INTO uk_postcodes (outcode, lat, lng)
VALUES ('AB13', '57.108', '-2.237');
Second way is using UNION ALL-
INSERT INTO uk_postcodes (outcode, lat, lng)
SELECT 'AB12', '57.098381', '-2.172400'
UNION ALL
SELECT 'AB13', '57.108', '-2.237'
With a bit of string replacing you will be able to convert the script into the following which should work:
INSERT INTO uk_postcodes (outcode, lat, lng)
select 'AB12', '57.098381', '-2.172400' union select 'AB13', '57.108', '-2.237'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
