2

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?

  • 3
    Upgrade to 2008 or better and this gets a whole lot easier. – Aaron Bertrand Sep 25 '15 at 17:27
  • 2
    I prefer Max's approach over UNION ALL SELECT because his is easier to separate, process in chunks, etc. Also, if the list has duplicates, you should make that clear in the question (we don't all scan the entire list of values to make sure they won't violate your constraints). – Aaron Bertrand Sep 25 '15 at 17:42
8

Since you are using SQL Server 2005, you need to perform a single insert for each value.

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.