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.

Answer 1

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