Dynamically creating a table 1

[Freekawaii]

I am trying to create a SQL executable string of type NVARCHAR that I can use the sp_executesql sp with. The purpose is to dynamically create a table, reading the table name from a table containing all tablenames from the sysobject file, and then building the table using the information_schema results.
Here is the resulting string
'CREATE TABLE [TRK3_AUTHS_MISSING_MEMBERS_Jun 19 2003 1:34PM](
[subscriber_ien] numeric(10,0),
[hmo] varchar(255),
[con] numeric(10,0),
[effective] datetime,
[termination] datetime,
[emp] varchar(31),
[u_appl_sign_dt] datetime,
[u_recd_dt] datetime,
[num] varchar(15),
[member_type] varchar(15),
[plan_type] varchar(31),
[plan_type_b] varchar(31),
[county] varchar(31),
[ctyst] varchar(80),
[fnm] varchar(31),
[lnm] varchar(31),
[ssn] varchar(11),
[street_address_1] varchar(31),
[street_address_2] varchar(31),
[u_tel_2] varchar(25),
[zip] varchar(10),
[emp_num] varchar(15),
[member_type_num] varchar(15),
[plan_type_num] varchar(15),
[plan_type_b_num] varchar(15),
[mid_init] varchar(15),
[pb_bill_option] varchar(20),
[u_pb_dw_bank] varchar(35),
[pb_dw_bank_acct] varchar(20),
[pb_dw_bank_acctyp] varchar(10),
[bank number] varchar(20),
[routing number] varchar(20),
[LIS UNIQUE (BANK) NUMBER] varchar(255))
I then do Execute sp_executesql @sqlstring
The results is a syntax error converting from nvarchare to
?? And it won't run
HELP!!!

 

[ukwiz]

You could try something along these lines...
create procedure #test
@sqlstring nvarchar(4000) =
'CREATE TABLE [TRK3_AUTHS_MISSING_MEMBERS_Jun 19 2003 1:34PM](
[subscriber_ien] numeric(10,0),
[hmo] varchar(255),
[con] numeric(10,0),
[effective] datetime,
[termination] datetime,
[emp] varchar(31),
[u_appl_sign_dt] datetime,
[u_recd_dt] datetime,
[num] varchar(15),
[member_type] varchar(15),
[plan_type] varchar(31),
[plan_type_b] varchar(31),
[county] varchar(31),
[ctyst] varchar(80),
[fnm] varchar(31),
[lnm] varchar(31),
[ssn] varchar(11),
[street_address_1] varchar(31),
[street_address_2] varchar(31),
[u_tel_2] varchar(25),
[zip] varchar(10),
[emp_num] varchar(15),
[member_type_num] varchar(15),
[plan_type_num] varchar(15),
[plan_type_b_num] varchar(15),
[mid_init] varchar(15),
[pb_bill_option] varchar(20),
[u_pb_dw_bank] varchar(35),
[pb_dw_bank_acct] varchar(20),
[pb_dw_bank_acctyp] varchar(10),
[bank number] varchar(20),
[routing number] varchar(20),
[LIS UNIQUE (BANK) NUMBER] varchar(255))'

as execute sp_executesql @sqlstring
go
execute dbo.#test
go

 

[Freekawaii]

Thanks!! I believe this may work!! Appreciate your help

Michelle

 

[ukwiz]

You wouldn't give it a star ;-)

 

[Freekawaii]

Sorry, You deserve it!! If you only knew how many DBA's have pondered this question and not been able to help. Once I figure out how to give you a star, I will!!

thanks again