Solved

sql add records to an existing table from a temp table

Posted on 2011-09-27
6
220 Views
Last Modified: 2012-05-12
I have a temporary table called temptransferdetail. There are several records in this table that have a column of information that I want to add into an existing table called products. I can select these records by the length of the column that I want to copy by:

select
distinct product
from temptransferdetail
where LEN(product) > 4

Open in new window


How can I copy that into the products table. The products table is two column, ID and Product. I want to copy the text from the above select into the Product column of the Products table.

thanks
0
Comment
Question by:wiggy353
  • 3
  • 2
6 Comments
 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 36711619
try this:

insert into myTable (product)
select
distinct product
from temptransferdetail
where LEN(product) > 4
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36711628
and hoping id is identity column that you do not need to populate...
0
 
LVL 1

Author Comment

by:wiggy353
ID: 36711679
id is identity column set to auto increment, but it still will not allow that insert into statement. It says that the id column cannot be null.
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36711694
If the ID column in the Products table has the IDENTITY property, you can simply do this:

INSERT INTO Products(Product)
SELECT DISTINCT product
FROM temptransferdetail
WHERE LEN(product) > 4

If not, do this:

Find out what the MAX ID for products is:

DECLARE @MAXID INT

SELECT @MAXID = MAX(ID) FROM Products

INSERT INTO Products(ID, Product)
SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY product) + @MAXID, product
FROM temptransferdetail
WHERE LEN(product) > 4

Greg

0
 
LVL 1

Author Closing Comment

by:wiggy353
ID: 36711875
The ID column was set to identity so the first suggestion should have worked, but for whatever reason it did not. Therefore I used the second suggestion. Thanks.
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36712193
it did not because you need to set insert identity on :)

SET IDENTITY_INSERT myTable ON;

insert statements here...

SET IDENTITY_INSERT myTable ON;



0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question