how to insert a list of varchar values, into a single column table?

Question

In SQL Server 2005:

CREATE TABLE __RADHE
(
    itemNo varchar(10) not null primary key clustered
);
GO

INSERT INTO __RADHE ???

'34926840', '34927020', '34927202', '34927384', '34927566',
'34927830', '34927889', '34930743', '34930750', '34927897',
'34927848', '34927574', '34927392', '34927210', '34927038',
'34926857', '34917120', '34917286', '34917443', '34926865',
'34927046', '34927228', '34927400', '34927582', '34927855',
'34927905', '34930768', '34930776', '34927913', '34927863',
'34927590', '34927418', '34927236', '34927053', '34926873',
'34917450', '34917294', '34917138', '34917146', '34917302',
'34917468', '34926881', '34927061', '34927244', '34927426',
'34927608', '34927871', '34927921', '34930784', '34927616',
'34927434', '34927251', '34927079', '34926899', '34917476',
'34917310', '34917153', '34917161', '34917328', '34917484',
'34926907', '34927087', '34927269', '34927442', '34927624',
'34927632', '34927459', '34927277', '34927095', '34926915',
'34917492', '34917336', '34917179', '34917187', '34917344',
'34917500', '34926923', '34927103', '34927285', '34927467',
'34927640', '34927657', '34927475', '34927293', '34927111',
'34926931', '34917518', '34917351', '34917195', '34917203',
'34917369', '34917526', '34926949', '34927129', '34927301',
'34927483', '34927665', '34927673', '34927491', '34927319',
'34927137', '34926956', '34917534', '34917377', '34917211',
'34917229', '34917385', '34917542', '34926964', '34927145',
'34927327', '34927509', '34927681', '34927699', '34927517',
'34927335', '34927152', '34926972', '34917559', '34917393',
'34917237', '34917245', '34917401', '34917567', '34926980',
'34927160', '34927343', '34927525', '34927707', '34927715',
'34927533', '34927350', '34927178', '34926998', '34917575',
'34917419', '34917252', '34917260', '34917427', '34917583',
'34927004', '34927186', '34927368', '34927541', '34927723',
'34927731', '34927558', '34927376', '34927194', '34927012',
'34917591', '34917435', '34917278', '34916577', '34916676',
'34933861', '34933879', '34929315', '34916684', '34916585',
'34909861', '34909879', '34916593', '34916692', '34929323',
'34933887', '34933895', '34929331', '34916700', '34916601',
'34909887', '34909895', '34916619', '34916718', '34929349',
'34933903', '34933911', '34929356', '34916726', '34916627',
'34909903', '34909911', '34916635', '34916734', '34929364',
'34933929', '34933937', '34929372', '34916742', '34916643',
'34909929', '34909937', '34916650', '34916759', '34929380',
'34933945'

How can I get this done?

Answer 1

Since you are using SQL Server 2005, you need to perform a single insert for each value. Upgrade to SQL Server 2012 or higher, and you get the multiple VALUES clause, making this much easier.

Something like:

INSERT INTO __RADHE (ItemNo)
VALUES ('34933945');

I would recommend using find-and-replace to replace the commas with the required syntax.

Replacing the commas in your value-list above with:

); INSERT INTO __RADHE (itemNo) VALUES (

Seems to get you almost all the way there.

As an aside; you should always specify the schema when referencing objects. When specifying PRIMARY KEY CLUSTERED, you are implicitly creating a constraint, which will be named by SQL Server if you don't specify a name. Specify a name if you don't like names similar to PK__T1__3214EC27023D5A04.

For instance, your CREATE TABLE statement should be something like:

CREATE TABLE dbo.[__RADHE]
(
    itemNo VARCHAR(10) NOT NULL 
        CONSTRAINT PK___RADHE
        PRIMARY KEY CLUSTERED
);

If there is a chance the source data contains duplicate entries, you may find it easier to delay creating the primary key constraint until the data has been inserted, and duplicates removed. To do that, create the table like this:

CREATE TABLE dbo.[__RADHE]
(
    itemNo VARCHAR(10) NOT NULL 
);

Then insert all the rows using the INSERT INTO ... statements as above.

Run this to remove duplicates:

;WITH src AS (
    SELECT r.*
        , rn = ROW_NUMBER() OVER (PARTITION BY r.itemNo ORDER BY r.itemNo)
    FROM dbo.__RADHE r
)
DELETE
FROM src
WHERE rn > 1;

Then create the primary key constraint:

ALTER TABLE dbo.__RADHE
ADD CONSTRAINT pk__RADHE
PRIMARY KEY CLUSTERED
(itemNo);

As a side note, unless you use a TABLOCKX lock hint on the table, all rows are individually logged using either the UNION ALL approach or the individual INSERT INTO approach I detail above. If you can afford to lock the entire table for the duration of the inserts via TABLOCKX, then only the page allocations are logged, vastly reducing the amount of logging that takes place. Having said all that, there are of course a lot of "gotchas" that might prevent bulk logging of pages - as detailed by Paul White here.