CODEWITHSUNDEEP
sql-server
BoundForGlory
BoundForGlory

Reputation: 4427

Insert records from one table to another with and user data..SQL server

I have the following sample t-sql statement:

 INSERT INTO [Northwind].[dbo].[Categories]([CategoryName], [Description],Picture)
 SELECT TOP 10 productname, quantityperunit FROM [Northwind].[dbo].Products

Very straight forward what I'm doing, BUT I want to add Picture into my table and I want to add it from my own data. I know this isn't correct, but i think it shows better than I can explain what i'm trying to do:

 INSERT INTO [Northwind].[dbo].[Categories]([CategoryName], [Description],Picture)
 (SELECT TOP 10 productname, quantityperunit FROM [Northwind].[dbo].Products),'this is dummy data not real!!'

So, the first 2 fields [CategoryName], [Description] I want to come from records in the Products table. I want the last field [Picture] to be populated with my own data.

This is just a sample of what i want to achieve. I want the data to be the same outside of the first 2 fields. I will not be using Northwind in production. I'm looking for syntax only. Thanks

Upvotes: 1

Views: 46

Answers (2)

GuidoG
GuidoG

Reputation: 12024

You where very close.
I dont know the northwind tables but if the column picture would be a varchar field than this would fill the 2 first columns from table products, and the 3th field with a fixed string. Is this what you mean ?

INSERT INTO [Northwind].[dbo].[Categories]
  ([CategoryName], [Description], Picture)
SELECT TOP 10 productname, quantityperunit, 'this is dummy data not real!!'
FROM [Northwind].[dbo].Products)

As you can see from @Shnugo 's answer and this answer the key is to just add static data to your select clause in your query.

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If your question is just: How do I add values to the insert list? you can look at this example:

DECLARE @tblSource TABLE(ID INT, SomeData VARCHAR(100));
INSERT INTO @tblSource VALUES(1,'Data 1'),(2, 'Data 2');

--The columns of the target could have other names, but the type must be compatible:

DECLARE @tblTarget TABLE(ID INT,SomeData VARCHAR(100),OneMore VARCHAR(100));

--Insert the two rows of @tblSource with one additional value

INSERT INTO @tblTarget(ID,SomeData,OneMore)
SELECT ID,SomeData,'Just add a static value into the list'
FROM @tblSource;

--Check the result

SELECT * FROM @tblTarget

The result

ID  SomeData    OneMore
1   Data 1      Just add a static value into the list
2   Data 2      Just add a static value into the list

Upvotes: 3

Related Questions