Adding a unique primary key when importing data

Question

Adding a unique primary key when importing data

bross-832576

Valued Member

Points: 55
More actions
July 8, 2010 at 1:42 am

#220862

Hi guys, this is my first post, so please excuse any 'rookie' errors.
I need to import data into an existing table from another system. The export from the other system is in .csv format. The data in the .csv file relates to 2 different tables. I have created temporary tables and input the required data into the first table with no issues. The problem I am experiencing is that the second table has a primary key that is set to SEQ_NUM autoincrement. What I need to do (I think) is to fetch the MAX number from the existing table and perfom a WHILE LOOP for the number of rows of data.
CREATE TABLE custom.plan(
customer_plan_dtl_seq SEQ_NUM,
customer_seq SEQ_NUM,
bonus_plan_seq SEQ_NUM)
grant all on custom.plan to CAEDC_GROUP;
grant all on custom.plan to DEFINITION_GROUP;
grant all on custom.plan to REPORTS_GROUP;
grant all on custom.plan to TRANSACTION_GROUP;
grant all on custom.plan to USER_GROUP;
BEGIN
DECLARE @n NUMERIC(10);
DECLARE @nr NUMERIC(10);
SET @n = 1;
SET @nr = (SELECT COUNT(*) FROM "custom"."info");
WHILE @n <= @nr LOOP
INSERT INTO "custom"."plan"
SELECT MAX (gp.customer_plan_dtl_seq)+@n FROM "sys"."customer_plan_dtl" gp,
i.customer_seq,
i.bonus_plan_seq
FROM "custom"."info" i;
SET @n = @n + 1;
END LOOP;
END
That gives me syntax errors on the line
SELECT MAX (gp.customer_plan_dtl_seq)+@n FROM "sys"."customer_plan_dtl" gp,
I have tried to replace that line with
SELECT @n,
Just to see if I can get it working without the MAX+@n bit, it does then execute but I don't get unique records for column customer_plan_dtl_seq what I do get is the entire import with the value 1 then the entire import with value 2 for all 614 rows of data.
I know that I may not have explained myself clearly, but I am not 100% sure of the syntax and terminology to use. Any assistance would be greatly appreciated.
Thanks in advance.
Brett

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

bross-832576 Valued Member Points: 55 More actions · Answer 1

With help from my friend Google, I have managed to get the MAX + 1 part right by declaring it as a variable first. New script as follows;

BEGIN

DECLARE @n NUMERIC(10);

DECLARE @nr NUMERIC(10);

DECLARE @mx NUMERIC(10);

SET @n = 1;

SET @nr = (SELECT COUNT(*) FROM "custom"."input");

SET @mx = (SELECT MAX (customer_plan_dtl_seq) FROM "sys"."customer_plan_dtl");

WHILE @n <= @nr LOOP

INSERT INTO "custom"."plan"

SELECT @mx + @n,

i.customer_seq,

i.bonus_plan_seq

FROM "custom"."input" i;

SET @n = @n + 1;

END LOOP;

END

The only problem I have now is with the LOOP part, it is still giving me all records with the first result of the LOOP and then all records with the second result of the LOOP and so on for the 614 rows.

Any ideas?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Brett

Please can you post the CREATE TABLE statement for <<the second table has a primary key that is set to SEQ_NUM autoincrement>>

Cheers

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bross-832576 Valued Member Points: 55 More actions · Answer 3

Hi Chris,

The table with the SEQ_NUM as autoincrement is an existing table in the database that I am eventually trying to import into. The table that I am creating has a SEQ_NUM but not set to autoincrement. I am trying to get the data into the right format before inputing it into the "live" table.

This is the whole script as it stands, I previously only posted the part I was having an issue with;

IF OBJECT_ID('custom.input') is not null

BEGIN

DROP TABLE "custom"."input"

END;

IF OBJECT_ID('custom.info') is not null

BEGIN

DROP TABLE "custom"."info"

END;

IF OBJECT_ID('custom.plan') is not null

BEGIN

DROP TABLE "custom"."plan"

END;

IF OBJECT_ID('custom.input_plan') is not null

BEGIN

DROP TABLE "custom"."input_plan"

END;

CREATE TABLE custom.input(

customer_seq SEQ_NUM,

bonus_plan_seq SEQ_NUM,

email_address VARCHAR(50),

user_defined_6 VARCHAR(50),

contact_title VARCHAR(50),

contact_first_name VARCHAR(30),

contact_last_name VARCHAR(50),

birthday VARCHAR(50),

user_defined_1 VARCHAR(254),

phone_number_3 VARCHAR(30),

address_line_1 VARCHAR(50),

address_line_2 VARCHAR(50),

state_or_province VARCHAR(20),

user_defined_2 VARCHAR(254),

city VARCHAR(50),

postal_code VARCHAR(20),

phone_number_1 VARCHAR(30),

phone_number_2 VARCHAR(30),

user_defined_7 VARCHAR(50),

user_defined_8 VARCHAR(50),

creation_date VARCHAR(50),

user_defined_3 VARCHAR(254),

user_defined_4 VARCHAR(254)

)

grant all on custom.gss_input to CAEDC_GROUP;

grant all on custom.gss_input to DEFINITION_GROUP;

grant all on custom.gss_input to REPORTS_GROUP;

grant all on custom.gss_input to TRANSACTION_GROUP;

grant all on custom.gss_input to USER_GROUP;

CREATE TABLE custom.info(

customer_seqSEQ_NUM,

group_seqSEQ_NUM,

creation_datedatetime,

contact_first_nameVARCHAR(30),

contact_last_nameVARCHAR(50),

address_line_1VARCHAR(50),

address_line_2VARCHAR(50),

cityVARCHAR(50),

state_or_provinceVARCHAR(20),

postal_codeVARCHAR(20),

countryVARCHAR(50),

contact_titleVARCHAR(50),

phone_number_1VARCHAR(30),

phone_number_2VARCHAR(30),

phone_number_3VARCHAR(30),

phone_number_4VARCHAR(30),

extensionVARCHAR(30),

fax_numberVARCHAR(30),

email_addressVARCHAR(50),

birthdaydatetime,

notesVARCHAR(254),

activeLOGICAL,

zoneVARCHAR(10),

hoaxLOGICAL,

card_numberVARCHAR(30),

complaintsVARCHAR(254),

delivery_notesVARCHAR(254),

courtesy_titleVARCHAR(20),

honorVARCHAR(30),

user_defined_1VARCHAR(254),

user_defined_2VARCHAR(254),

user_defined_3VARCHAR(254),

user_defined_4VARCHAR(254),

user_defined_5VARCHAR(254),

user_defined_6VARCHAR(50),

user_defined_7VARCHAR(50),

user_defined_8VARCHAR(50),

user_defined_9VARCHAR(50),

user_defined_10VARCHAR(50),

cc_card_numberVARCHAR(30),

cc_expire_dateVARCHAR(10),

phone_data_1VARCHAR(30),

phone_data_2VARCHAR(30),

phone_data_3VARCHAR(30),

phone_data_4VARCHAR(30),

encrypt_value_seqSEQ_NUM_EX,

invalid_addressLOGICAL);

grant all on custom.gss_info to CAEDC_GROUP;

grant all on custom.gss_info to DEFINITION_GROUP;

grant all on custom.gss_info to REPORTS_GROUP;

grant all on custom.gss_info to TRANSACTION_GROUP;

grant all on custom.gss_info to USER_GROUP;

CREATE TABLE custom.plan(

customer_plan_dtl_seq SEQ_NUM,

customer_seq SEQ_NUM,

bonus_plan_seq SEQ_NUM)

grant all on custom.gss_info to CAEDC_GROUP;

grant all on custom.gss_info to DEFINITION_GROUP;

grant all on custom.gss_info to REPORTS_GROUP;

grant all on custom.gss_info to TRANSACTION_GROUP;

grant all on custom.gss_info to USER_GROUP;

CREATE TABLE custom.input_plan(

seq SEQ_NUM,

customer_plan_dtl_seq SEQ_NUM)

grant all on custom.gss_info to CAEDC_GROUP;

grant all on custom.gss_info to DEFINITION_GROUP;

grant all on custom.gss_info to REPORTS_GROUP;

grant all on custom.gss_info to TRANSACTION_GROUP;

grant all on custom.gss_info to USER_GROUP;

INPUT INTO custom.input

FROM d:\\export.csv;

INSERT INTO custom.info

SELECT i.customer_seq,

'',

IF i.creation_date like '00%' THEN CURRENT TIMESTAMP ELSE DATEFORMAT(i.creation_date, 'yyyy-mm-dd 00:00:00.000'

i.contact_first_name,

i.contact_last_name,

i.address_line_1,

i.address_line_2,

i.city,

i.state_or_province,

i.postal_code,

'',

i.contact_title,

i.phone_number_1,

i.phone_number_2,

i.phone_number_3,

'',

i.email_address,

IF i.birthday like '00%' THEN CURRENT TIMESTAMP ELSE DATEFORMAT(i.birthday, 'yyyy-mm-dd 00:00:00.000') ENDIF,

'',

'T',

'',

'F',

'',

i.user_defined_1,

i.user_defined_2,

i.user_defined_3,

i.user_defined_4,

'',

i.user_defined_6,

i.user_defined_7,

i.user_defined_8,

'',

'F'

FROM custom.input i;

SELECT * FROM "custom"."info"

EXCEPT

SELECT * FROM "sys"."customer_def";

OUTPUT TO d:\\inputin.csv;

INPUT INTO "sys"."customer_def"

FROM d:\\inputin.csv;

BEGIN

DECLARE @n NUMERIC(10);

DECLARE @nr NUMERIC(10);

DECLARE @mx NUMERIC(10);

SET @n = 1;

SET @nr = (SELECT COUNT(*) FROM "custom"."input");

SET @mx = (SELECT MAX (customer_plan_dtl_seq) FROM "sys"."customer_plan_dtl");

WHILE @n <= @nr LOOP

INSERT INTO "custom"."input_plan"(seq, customer_plan_dtl_seq)

VALUES (@n, @mx + @n);

SET @n = @n + 1;

END LOOP;

END

The plan after I can get the info in correctly is to finish off with a similar query to only import the new data;

SELECT * FROM "custom"."plan"

EXCEPT

SELECT * FROM "sys"."customer_plan_dtl";

OUTPUT TO d:\\planin.csv;

INPUT INTO "sys"."customer_plan_dtl"

FROM d:\\planin.csv;

I hope this clears some things up, I really appreciate the help!

Brett

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Brett, if the table you are eventually trying to import into has an autoincrementing identity column, then why not use it?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bross-832576 Valued Member Points: 55 More actions · Answer 5

Chris,

I could do that, and possibly simplify my life quite a bit, I didn't know that I could do that. Can I import into a table with an autoicrement SEQ_NUM without specifying a value for that column. the final table has 3 columns in it the SEQ_NUM and two columns with data already in a different table. How can I get the 2 columns in with the first column autoincrementing?

That would be a great help!

Thanks

Brett

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Hi Brett

You have to use a column list, as in this example:

DROP TABLE #Target

DROP TABLE #Source

CREATE TABLE #Target ([ID] INT IDENTITY(1, 1), Something VARCHAR(10), SomethingElse VARCHAR(10))

CREATE TABLE #Source (Something VARCHAR(10), SomethingElse VARCHAR(10))

INSERT INTO #Source (Something, SomethingElse)

SELECT 'Black', 'White' UNION ALL

SELECT 'Gin', 'Tonic' UNION ALL

SELECT 'Rubarb', 'Custard' UNION ALL

SELECT 'Trinidad', 'Tobago'

INSERT INTO #Target (Something, SomethingElse)

SELECT Something, SomethingElse

FROM #Source

SELECT * FROM #Target

INSERT INTO #Target (Something, SomethingElse)

SELECT Something, SomethingElse

FROM #Source

SELECT * FROM #Target

The column list must exclude the autoincrementing column.

You can include the autoincrementing column if you SET IDENTITY_INSERT ON but you then have to provide unique values.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bross-832576 Valued Member Points: 55 More actions · Answer 7

Chris,

Thanks for that, I will modify it accordingly and give it a try.

Thanks again for your assitance.

brett