?
Solved

sql add records to an existing table from a temp table

Posted on 2011-09-27
6
Medium Priority
?
230 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 58

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 36711619
try this:

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

Expert Comment

by:HainKurt
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
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 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 58

Expert Comment

by:HainKurt
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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

764 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