How to insert into table based on previous results

Question

How to insert into table based on previous results

jbuttery

Ten Centuries

Points: 1226
More actions
April 6, 2009 at 2:23 pm

#132663

I can't seem to figure how to insert new records into a database based on previous records. I want to read a record, increment or modify several columns and insert the results as a new record. There is an identity column. I trying to build a test database having several thousand records to one having several million. Any info on how do this using bulk insert would be great.

Viewing 15 posts - 1 through 15 (of 19 total)

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

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

jbuttery (4/6/2009)
I can't seem to figure how to insert new records into a database based on previous records. I want to read a record, increment or modify several columns and insert the results as a new record. There is an identity column. I trying to build a test database having several thousand records to one having several million. Any info on how do this using bulk insert would be great.

INSERT INTO Mytable (column list)

SELECT column list (increment or modify several columns)

FROM Mytable

WHERE ...

Exclude the identity column from the column list, alternatively set identity insert on.

^{“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

jbuttery Ten Centuries Points: 1226 More actions · Answer 2

INSERT INTO Mytable (col1, col2, col3, col4)

SELECT col1, col2, col3, col4

FROM Mytable

Set col1 = col1 + 1

Set col2 = col2 + 1

Set col3 = col3 + 1

Set col4 = col4 + 1

Where ...

Am I closer?

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

Yes, that's pretty much it. As you can see, you can do a whole set at a time, no fiddling about with single rows.

What do you want to do with your identity column?

^{“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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi

I'm not sure if I got you, but I'll try...

Do you mean this?

INSERT INTO Mytable (col1, col2, col3, col4)

SELECT col1 + 1, col2 + 1, col3 + 1, col4 + 1

FROM Mytable

If not you maybe should post some sample data and your expected result.

Greets

Flo

jbuttery Ten Centuries Points: 1226 More actions · Answer 5

Existing data in row 1

Col0(Identity)Col1 Col2 Col3 Col4

1 123123123444444444555555555666666666

should generate the following with 2 loops

2 123123124444444445555555556666666667

3 123123125444444446555555557666666668

I'll test and see if I need insert identity or not

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

jbuttery (4/7/2009)
Existing data in row 1
Col0(Identity)Col1 Col2 Col3 Col4
1 123123123444444444555555555666666666
should generate the following with 2 loops
2 123123124444444445555555556666666667
3 123123125444444446555555557666666668
I'll test and see if I need insert identity or not

Can you construct a SELECT which outputs the rows and columns (with math) that you want to INSERT?

^{“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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

jbuttery (4/7/2009)
Existing data in row 1
Col0(Identity)Col1 Col2 Col3 Col4
1 123123123444444444555555555666666666
should generate the following with 2 loops
2 123123124444444445555555556666666667
3 123123125444444446555555557666666668
I'll test and see if I need insert identity or not

Did you have a look to my example?

Greets

Flo

jbuttery Ten Centuries Points: 1226 More actions · Answer 8

Thanks for the replies guys. I'll try suggestions. I can do a SELECT, but not sure how to manipulate data and then insert.

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

jbuttery (4/7/2009)
Thanks for the replies guys. I'll try suggestions. I can do a SELECT, but not sure how to manipulate data and then insert.

Post the select, Flo will show you how to do the rest 😉

^{“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

jbuttery Ten Centuries Points: 1226 More actions · Answer 10

INSERT INTO Ex_Items (ReviewDate, Hub, BankNumber, BranchNumber, AccountNumber)

SELECT ReviewDate, Hub, BankNumber, BranchNumber, (AccountNumber + 1)

FROM Ex_Items

Causes:

The conversion of the varchar value '000000005121321212' overflowed an int column. Maximum integer value exceeded.

The column is defined as char(18). Where did varchar and int come from?

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

What datatype is account number? That's where your problem is - it's either char or varchar and SQL Server tries to convert it to an int to add the 1. It's too big for an INT. Try casting it to bigint in the SELECT before adding the 1 - then we can put back those pesky leading zeroes.

^{“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

jbuttery Ten Centuries Points: 1226 More actions · Answer 12

I didn't see my last post. I'm testing with 1 iteration first. Once it works I'll figure out a bulk method.

INSERT INTO Ex_ItemsException (ReviewDate, Hub, BankNumber, BranchNumber, AccountNumber)

SELECT ReviewDate, Hub, BankNumber, BranchNumber, (AccountNumber + 1)

FROM Ex_ItemsException

Caused:

The conversion of the varchar value '000000005121321212' overflowed an int column. Maximum integer value exceeded.

AccountNumber is char(18). Where did varchar and int come from?

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

RIGHT('00000000000000000' + CAST(CAST('000000005121321212' AS BIGINT) + 1 AS VARCHAR(18)), 18)

^{“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

jbuttery Ten Centuries Points: 1226 More actions · Answer 14

I guess it's kinda hard to add 1 to a char field. I see what your trying to do Chris. I need to cast the char as a bigint, increment by 1, convert back to char and left fill with 0 for a length of 18. Yes?