Insert fixed values and from another table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert fixed values and from another table?

Author

Topic

Pirre0001
Starting Member

19 Posts

Posted - 2014-05-01 : 08:43:47

I have a table (tblCustomer) with three fields (customer_id, s_id, s_string)
I want to fill in this table with two fixed values ??and customer_id from another table.

Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.


Select customer_id from tblMainCustomer where customer_nr like 'P%'

Customer_id taken from tblMainCustomer and s_id, s_string these fixed values ??that are equal for each customer.
These fixed values ??are:


100-----Gold
101-----Steel
1002----Super Copper

Example:
If I have a client who has has a customer_id 45 so it will be like this in tblCustomer:


customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper

I am stuck, how do I do this the best way?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-01 : 08:56:28

Please post samples:

1. DDL (Create Table ... for both tables)
2. DML (Insert Into ... for both tables)
3. A sample of the result you with to see.

Pirre0001
Starting Member

19 Posts

Posted - 2014-05-01 : 09:25:48

Fixed values:


DECLARE @s_id_1 int
SET @s_id_1 = 100
DECLARE @s_string_1 nvarchar(35)
SET @s_string_1 = 'Gold'

DECLARE @s_id_2 int
SET @s_id_2 = 101
DECLARE @s_string_2 nvarchar(35)
SET @s_string_2 = 'Steel'

DECLARE @s_id_3 int
SET @s_id_3 = 102
DECLARE @s_string_3 nvarchar(35)
SET @s_string_3 = 'Super Copper'


CREATE TABLE tblCustomer
(
customer_id int,
s_id int,
s_string nvarchar(35)
);

CREATE TABLE tblMainCustomer
(
customer_id int,
customer_nr nvarchar(20),
customer_name nvarchar(40)
);

INSERT INTO tblMainCustomer ( customer_id, customer_nr, customer_name) VALUES
( 45,'P1432','Toyota' ), ( 34,'E4321','Volvo' ), ( 64,'P2342','Honda' ), ( 171,'P8312','Nissan' );

The result in tblCustomer should be like this:


customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper
64-------------100-----Gold
64-------------101-----Steel
64-------------102-----Super Copper
171------------100-----Gold
171------------101-----Steel
171------------102-----Super Copper

quote:
Originally posted by gbritton

Please post samples:

1. DDL (Create Table ... for both tables)
2. DML (Insert Into ... for both tables)
3. A sample of the result you with to see.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-01 : 10:17:58

This ought to do it:


insert into tblcustomer
select customer_id, cj.s_Id, cj.s_string
from tblMainCustomer
cross join (
	select * from (values
		(@s_Id_1, @s_string_1),
		(@s_Id_2, @s_string_2),
		(@s_Id_3, @s_string_3)
	) vals(s_Id, s_string)) cj

Pirre0001
Starting Member

19 Posts

Posted - 2014-05-01 : 10:44:00

Thanks!
I only wanna do this for customer who begins with P in tblMainCustomer.customer_nr.
I guess I can add where cluse like this...


insert into tblcustomer
select customer_id, cj.s_Id, cj.s_string
from tblMainCustomer
where customer_nr like 'P%'
cross join (
	select * from (values
		(@s_Id_1, @s_string_1),
		(@s_Id_2, @s_string_2),
		(@s_Id_3, @s_string_3)
	) vals(s_Id, s_string)) cj

quote:

Originally posted by gbritton

This ought to do it:


insert into tblcustomer
select customer_id, cj.s_Id, cj.s_string
from tblMainCustomer
where customer_nr like 'P%'
cross join (
	select * from (values
		(@s_Id_1, @s_string_1),
		(@s_Id_2, @s_string_2),
		(@s_Id_3, @s_string_3)
	) vals(s_Id, s_string)) cj

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-02 : 01:08:53

Hi Pierre ,
If you use WHERE clause before Cross join you will get error..
you can do it as below


INSERT INTO tblCustomer1 
SELECT customer_id,cj.s_Id,cj.s_string FROM tblMainCustomer
CROSS JOIN
(
	SELECT * FROM (VALUES (@s_Id_1, @s_string_1),(@s_Id_2, @s_string_2),(@s_Id_3, @s_string_3)) a(s_Id,s_string))CJ

WHERE customer_nr LIKE 'P%'

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Tusharp86
Starting Member

9 Posts

Posted - 2014-05-02 : 02:24:35

Hi

Its good use of Table Value Constructor in SQL. Most Experienced Developer even don't know this constructor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources