Solved

sql add records to an existing table from a temp table

Posted on 2011-09-27
6
194 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:HainKurt
HainKurt earned 250 total points
Comment Utility
try this:

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

Expert Comment

by:HainKurt
Comment Utility
and hoping id is identity column that you do not need to populate...
0
 
LVL 1

Author Comment

by:wiggy353
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
Comment Utility
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
Comment Utility
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:HainKurt
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
CREATE DATABASE ENCRYPTION KEY 1 40
Sql query for filter 12 18
Troubleshooting Methodology - steps 3 13
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now