SQL Combine two statements from old tabels into a new one

Question

I want to create a new table in a stored procedure. In the new table I need a row with a combined value from the old tables.

It should look like sees

 Table_old1      Table_old2   Table_new
 ----------------------------------------
 Edward          Mary         EdwardMary 
 Daniel          John         DanielJohn
 George          Sam          GeorgeSam
 Steven          Alaina       StevenAlaina
 Paul            Edward       PaulEdward

For the stored procedure I use some parameters for dynamic SQL.

I tried the following code my result was no success at all ;(

CREATE PROCEDURE build_together 
    @tblname sysname 
AS
   DECLARE @sql nvarchar(4000)

   SELECT @sql = ' CREATE TABLE all_together AS('
                 ' SELECT all.Values, choosen.Values ' +
                 'INTO all_together'+
                 ' FROM dbo.tbl_all_possible all, dbo.' + quotename(@tblname) + ' choosen);' 

   EXEC sp_executesql @sql

Any insight would be greatly appreciated.

Your current query would create a cartesian product. What key do you use to join the tables together? What field do the tables have in common? — crthompson, Jul 23 '14 at 13:25
What do your existing tables look like? In your example, how do you know Edward joins to Mary, etc? — Andrew, Jul 23 '14 at 13:25
Also i think your dynamic query statement formed has syntax issues — Recursive, Jul 23 '14 at 13:26
Are you trying to generate some test data? No join clause or in your case, the absence of where clause suggests you are creating a Cartesian Product (Cross Join) for test data right? — Gouri Shankar Aechoor, Jul 23 '14 at 15:11
Your example is trying to match table row-by-row but SQL Server doesn't work like that. It works with a set mentality. The only thing that connects 2 datasets is the relationship. How else would SQL Server knows "Edward" should be connected to "Mary"? — Code Different, Jul 23 '14 at 22:20

score 0 · Answer 1 · edited Jul 23 '14 at 13:41

Just try this out,may not be correct as you didn't mention any key columns, just gave it a spin.

SELECT old1.name as oldname1,old2.name as oldname2,old1.name+old2.name as newname AS name
                (SELECT name,row_number() OVER (ORDER BY (SELECT NULL)) AS id FROM old_table1 )old1
                JOIN
                (SELECT name,row_number() OVER (ORDER BY (SELECT NULL)) AS id FROM old_table2 )old2
                ON old1.id=old2.id

C.J. · Answer 2 · 2014-07-23T13:51:06.833

You can use simple JOIN and CONCATENATE to achieve this.

Try the following:

CREATE PROCEDURE build_together

AS
CREATE TABLE Table_New
--Insert column names here

INSERT INTO Table_New
SELECT old1.ColumnName + old2.ColumnName AS New_ColumnName
FROM Table_old1 old1, Table_old2 old2
--If you have any where condition or specific Primary key that you need to match,
--then add a where clause:
WHERE old1.Primary_Key = old2.Primary_Key

score 0 · Accepted Answer · answered Jul 23 '14 at 15:08

I am guessing you want to generate some sample data. This should be useful and if you want to modify and add Joins, that should be easy as well.

SCRIPT:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_BuildTogether]') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.usp_BuildTogether
GO

CREATE PROCEDURE usp_BuildTogether 
     @OneTableName sysname = 'tbl_all_possible'
    ,@OneColumnName sysname = 'Value'
    ,@TwoTableName sysname = 'tbl_all_possible2'
    ,@TwoColumnName sysname = 'Value'
    ,@CombinedTableName sysname = 'all_together'
AS
BEGIN
   DECLARE @sql nvarchar(4000) = NULL

   SELECT @sql = 'IF object_id(N'''+@CombinedTableName+''')>0'+CHAR(13)+CHAR(10)+
                 'BEGIN'+CHAR(13)+CHAR(10)+
                 '  Drop table '+@CombinedTableName+CHAR(13)+CHAR(10)+
                 'END'+CHAR(13)+CHAR(10)
   EXEC (@sql)
   SELECT @sql = 'SELECT one.'+@OneColumnName+' AS Table_old1, two.'+@TwoColumnName+' as Table_old2, one.'+@OneColumnName+'+'' ''+two.'+@TwoColumnName+' as  Table_new'+CHAR(13)+CHAR(10)+
                 'INTO '+@CombinedTableName+CHAR(13)+CHAR(10)+
                 'FROM '+QUOTENAME(@OneTableName)+' one, '+QUOTENAME(@TwoTableName)+' two;'
   EXEC (@sql)
END

TEST DATA:

--Cleanup Old test tables
IF object_id(N'all_together')>0
BEGIN
    Drop table all_together
END

IF object_id(N'tbl_all_possible')>0
BEGIN
    Drop table tbl_all_possible
END
Create Table tbl_all_possible (ID INT IDENTITY(1,1) ,Value varchar(250))
IF object_id(N'tbl_all_possible2')>0
BEGIN
    Drop table tbl_all_possible2
END
Create Table tbl_all_possible2 (ID INT IDENTITY(1,1) ,Value varchar(250))

-- Insert Test Data
INSERT INTO tbl_all_possible
SELECT 'Edward' UNION ALL
SELECT 'Daniel' UNION ALL
SELECT 'George' UNION ALL
SELECT 'Steven' UNION ALL
SELECT 'Paul' 

INSERT INTO tbl_all_possible2
SELECT 'Mary' UNION ALL
SELECT 'John' UNION ALL
SELECT 'Sam' UNION ALL
SELECT 'Alaina' UNION ALL
SELECT 'Edward' 

--Execute SP
EXEC SP_EXECUTESQL usp_BuildTogether

--Verify Result
SELECT * from all_together

--Cleanup
IF object_id(N'tbl_all_possible')>0
BEGIN
    Drop table tbl_all_possible
END
IF object_id(N'tbl_all_possible2')>0
BEGIN
    Drop table tbl_all_possible2
END
IF object_id(N'all_together')>0
BEGIN
    Drop table all_together
END

SQL Combine two statements from old tabels into a new one

3 Answers3