April 7, 2011 at 10:46 pm
Hi, I need help. I need to insert the data from vendor into VendorCOID but, somehow I need to insert all rows for each department in the department table and set the Dept_CD from the department table.
For example, I have ten records in the vendor table and I have three departments in the department table. Well, in the VendorCOID table I need to have 30 records, 10 for each department, 10 for AA, 10 for AB, and 10 for AC.
But, in the vendorCOID, I need to set the COID with the three Dept_CD codes in the department table.
I know it's confusing and is not a good practice because the vendor data is duplicated. But, this is what I need to do.
I will appreciate any help.
Below are the scripts for the sample tables and data.
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Department1](
[DEPT_CD] [varchar](4) NULL,
[DEPT_NM] [varchar](60) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [Department1] (DEPT_CD, DEPT_NM)
VALUES ('AA', 'Test Dept 1');
INSERT INTO [Department1] (DEPT_CD, DEPT_NM)
VALUES ('AB','Test Dept 2');
INSERT INTO [Department1] (DEPT_CD, DEPT_NM)
VALUES ('AC','Test Dept 3');
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Vendor](
[VEND_CUST_CD] [varchar](20) NULL,
[COMP_NM] [varchar](60) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('01', 'Vendor 1');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('02','Vendor 2');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('03','Vendor 3');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('04', 'Vendor 4');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('05','Vendor 5');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('06','Vendor 6');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('07', 'Vendor 7');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('08','Vendor 8');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('09','Vendor 9');
INSERT INTO [Vendor] (VEND_CUST_CD, COMP_NM)
VALUES ('10','Vendor 10');
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VendorCOID](
[COID] [varchar](12) NULL,
[ERPVendorNumber] [varchar](20) NULL,
[VendorName] [varchar](60) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
April 7, 2011 at 10:55 pm
First, thank you very much for providing the DDL and DML to help us help you. The only thing you could have done better was to provide an example of exactly what you wanted the result to look like. (In any case I wish more people would provide at least as much as you did!)
I think you just want to CROSS JOIN the two tables, if so this should do it for you:
/* Cross join the Department1 table and the Vendor table to populate the VendorCOID table */
INSERT INTO dbo.VendorCOID
SELECT
Dep.DEPT_CD,
Vend.VEND_CUST_CD,
Vend.COMP_NM
FROM dbo.Department1 Dep
CROSS JOIN dbo.Vendor Vend;
/* Return the data to see if it is what you want */
SELECT
*
FROM dbo.VendorCOID;
Let me know if that is what you were looking for or not. (I wasn't positive since the column names differ between tables.)
April 8, 2011 at 12:26 pm
Thank you..
April 8, 2011 at 1:35 pm
Your welcome, and thanks for letting me know that it helped.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply