Start Free Trial

asked on

Copy files from one field in one table into many fields on another table.

I have one field that holds a number composed for either '0' or '1' something like: 10010111101
I want to take the first digit and copy it to another field on a different table, then take the second digit and copy it to field number 2 of the other table and so on. There are 11 digits.

Table name with the 11 digits is: UserLogin
field in UserLogin table that has the digits: Accessrights

Table to which I want to copy the digits: contact_accessrights

The names of the fields in order is:

Pprofile
CGeneralInfo
Catty
Cparties
Ccomments
Cdocs
Cbilling
Csteps
RelCases
MainCases
Cforms

So the first digit should be copied to "Pprofile" , the second digit to "CGeneralInfo", etc.
There are also one to one fields I need to copy.

userid to userid ... same name in both tables and also firmid and userloginid ... three one to one fields.

Help is appreciated. Using MS SQL 2008.

You would need to use substr to part the info in the query.

You would need to use a variable assignment
Or use a stored procedure, presumably the destination and the source table have a common reference/identity column.

ASKER

Well ... then. I have no clue what that means :#

Please post the create table if the one in which the data is in, and the obe into which it will go.

ASKER

It should be something like this:

INSERT INTO dbo.contact_accessrights (id ,
firmid ,
userid ,
userloginid ,
sec_contactmodule ,
sec_questionnaire ,
sec_contacts ,
sec_cases ,
sec_employers ,
sec_billing ,
sec_regionalcenters ,
sec_agents ,
sec_messages ,
sec_reports ,
sec_blankforms ,
sec_usefullinks ,
sec_calendar ,
sec_resourcecenter ,
sec_usefullinks ,
cases_general ,
cases_links ,
cases_forms ,
cases_notes ,
cases_reminders ,
cases_documents ,
cases_steps ,
cases_main ,
cases_related ,
activity_delete ,
activity_EB5 ,
activity_upload ,
billing_edit ,
billing_delete ,
billing_trustaccounts)
VALUES
Select

(firmid,
userid ,
userloginid,
IndvClientModule,
1 ,
CMSContacts ,
CMSCases ,
CMSEmployers,
Accessrights (Digit 7) ,
1 ,
1 ,
1 ,
CMSReports ,
1 ,
Usefulinks ,
1 ,
RCAccess ,
Accessrights (Digit 2) ,
Accessrights (Digit 3) ,
Accessrights (Digit 4) ,
Accessrights (Digit 5) ,
Accessrights (Digit 6) ,
Accessrights (Digit 8) ,
Accessrights (Digit 9) ,
Accessrights (Digit 10) ,
Accessrights (Digit 11) ,
1 ,
Deletefiles ,
0 ,
Uploadocs ,
SecBillingedit ,
SecBillingdelete ,
BillingTrstaccnt , );
FROM Userlogin

All those are the names of the field in the 'userlogin' table. I just don't know the syntax so that it takes that value from the table and inserts it into the corresponding column above.

Substring(accessrights,position,1) is the function to extract a single character based on the location of interest.

Where position is the number from 1 to 11 1indicating the first and the 11 meaning the last.
Use that with the position adjustment to replace your accessrights (position) reference.

ASKER

Ok, but is the rest of the syntax correct ? Select (fields) from table ?

ASKER

I have this but I am getting a syntax error: Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ','.

---
INSERT INTO dbo.contact_accessrights
(firmid ,
userid ,
userloginid ,
sec_contactmodule ,
sec_questionnaire ,
sec_contacts ,
sec_cases ,
sec_employers ,
sec_billing ,
sec_regionalcenters ,
sec_agents ,
sec_messages ,
sec_reports ,
sec_blankforms ,
sec_usefullinks ,
sec_calendar ,
sec_resourcecenter ,
sec_usefullinks ,
cases_general ,
cases_links ,
cases_notes ,
cases_reminders ,
cases_documents ,
cases_steps ,
cases_related ,
cases_main ,
cases_forms,
activity_delete ,
activity_EB5 ,
activity_upload ,
billing_edit ,
billing_delete ,
billing_trustaccounts)

SELECT

(FirmId,
UserId,
UserLoginId,
IndvClientModule,
1 ,
CMSContacts ,
CMSCases ,
CMSEmployers,
SUBSTRING(AccessRights, 7, 1)AS sec_billing ,
1 ,
1 ,
1 ,
CMSReports ,
1 ,
Usefulinks ,
1 ,
RCAccess ,
1,
SUBSTRING(AccessRights, 2, 1) AS cases_general ,
SUBSTRING(AccessRights, 3, 1) AS cases_links,
SUBSTRING(AccessRights, 5, 1) AS case_notes,
1,
SUBSTRING(AccessRights, 6, 1) AS case_documents,
SUBSTRING(AccessRights, 8, 1) AS case_steps,
SUBSTRING(AccessRights, 9, 1) AS cases_related,
SUBSTRING(AccessRights, 10, 1) AS cases_main,
SUBSTRING(AccessRights, 11, 1) AS cases_forms,
Deletefiles ,
0 ,
Uploadocs ,
SecBillingedit ,
SecBillingdelete ,
BillingTrstaccnt )
FROM Userlogin

You can have it in the form of

Insert into tablename values (select )

Making sure the
In your case you are missing the values (select )

Insert into tablename (column list) values ( select columns list from tablenamesource)

Move the parenthesis on the select to make select to be inside and add values where between them. And move the closing parenthesis after the from tablename.

The for insert into tablename values (select the columns names must be listed and include all required not including the auto increment/identity column if any.

You can in the select use as to name the columns of the query to match the table into which you want the data inserted.

ASKER

I did but I get an syntax error on the first line and in the SELECT

INSERT INTO
dbo.contact_accessrights
(firmid ,
userid ,
userloginid ,
sec_contactmodule ,
sec_questionnaire ,
sec_contacts ,
sec_cases ,
sec_employers ,
sec_billing ,
sec_regionalcenters ,
sec_agents ,
sec_messages ,
sec_reports ,
sec_blankforms ,
sec_usefullinks ,
sec_calendar ,
sec_resourcecenter ,
sec_usefullinks ,
cases_general ,
cases_links ,
cases_notes ,
cases_reminders ,
cases_documents ,
cases_steps ,
cases_related ,
cases_main ,
cases_forms,
activity_delete ,
activity_EB5 ,
activity_upload ,
billing_edit ,
billing_delete ,
billing_trustaccounts)

VALUES

( SELECT
FirmId,
UserId,
UserLoginId,
IndvClientModule,
1 ,
CMSContacts ,
CMSCases ,
CMSEmployers,
SUBSTRING(AccessRights, 7, 1)AS sec_billing ,
1 ,
1 ,
1 ,
CMSReports ,
1 ,
Usefulinks ,
1 ,
RCAccess ,
1,
SUBSTRING(AccessRights, 2, 1) AS cases_general ,
SUBSTRING(AccessRights, 3, 1) AS cases_links,
SUBSTRING(AccessRights, 5, 1) AS case_notes,
1,
SUBSTRING(AccessRights, 6, 1) AS case_documents,
SUBSTRING(AccessRights, 8, 1) AS case_steps,
SUBSTRING(AccessRights, 9, 1) AS cases_related,
SUBSTRING(AccessRights, 10, 1) AS cases_main,
SUBSTRING(AccessRights, 11, 1) AS cases_forms,
Deletefiles ,
0 ,
Uploadocs ,
SecBillingedit ,
SecBillingdelete ,
BillingTrstaccnt
FROM Userlogin)
error1.PNG
error2.PNG

If you highlight only the select, does it run and return data?

ASKER

It does.

ASKER

I selected 'debug'. This is what I got:

Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 75
Incorrect syntax near ')'.

ASKER

I added names to each column but I still get the same error:

INSERT INTO 
dbo.contact_accessrights 
		(firmid ,
        userid ,
        userloginid ,
        sec_contactmodule ,
        sec_questionnaire ,
        sec_contacts ,
        sec_cases ,
        sec_employers ,
        sec_billing ,
        sec_regionalcenters ,
        sec_agents ,
        sec_messages ,
        sec_reports ,
        sec_blankforms ,
        sec_usefullinks ,
        sec_calendar ,
        sec_resourcecenter ,
        sec_usefullinks ,
        cases_general ,
        cases_links ,
        cases_notes ,
        cases_reminders ,
        cases_documents ,
        cases_steps ,
        cases_related ,
        cases_main ,
        cases_forms,
        activity_delete ,
        activity_EB5 ,
        activity_upload ,
        billing_edit ,
        billing_delete ,
        billing_trustaccounts)
 
 
 VALUES 
   
  (SELECT 
  FirmId,
  UserId,
  UserLoginId,
  IndvClientModule,
        1 AS sec_questionnaire,
        CMSContacts ,
        CMSCases ,
        CMSEmployers,
        SUBSTRING(AccessRights, 7, 1)AS sec_billing ,
        0 AS sec_regionalcenters,
        0 AS sec_agents,
        1 AS sec_messages,
        CMSReports ,
        1 AS sec_blankforms,
        Usefulinks ,
        1 AS sec_calendar,
        RCAccess ,
        1 AS sec_usefullinks,
        SUBSTRING(AccessRights, 2, 1) AS cases_general ,
        SUBSTRING(AccessRights, 3, 1) AS cases_links,
        SUBSTRING(AccessRights, 5, 1) AS case_notes,
        1 AS cases_reminders,
        SUBSTRING(AccessRights, 6, 1) AS case_documents,
        SUBSTRING(AccessRights, 8, 1) AS case_steps,
        SUBSTRING(AccessRights, 9, 1) AS cases_related,
        SUBSTRING(AccessRights, 10, 1) AS cases_main,
        SUBSTRING(AccessRights, 11, 1) AS cases_forms,
        Deletefiles ,
        0 AS activity_EB5,
        Uploadocs ,
        SecBillingedit ,
        SecBillingdelete ,
        BillingTrstaccnt 
 FROM Userlogin)

Open in new window

Error: Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 74
Incorrect syntax near ')'.

Does the select by itself run?

Remove the opening/closing parenthesis surrounding the select query

Insert into tablename (columns) values select columns from tablename

ASKER

I did and the word 'Select' is marked with red. I get this error if I try to run the query:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'SELECT'.

select * from userLogin
What happens?

ASKER

It shows all the data :)

and if you run the
select FirmID,UserID, substring(Accessrights,1,1) from userlogin

What happens?

Does the following return data?

SELECT 
  FirmId,
  UserId,
  UserLoginId,
  IndvClientModule,
        1 AS sec_questionnaire,
        CMSContacts ,
        CMSCases ,
        CMSEmployers,
        SUBSTRING(AccessRights, 7, 1) AS sec_billing ,
        0 AS sec_regionalcenters,
        0 AS sec_agents,
        1 AS sec_messages,
        CMSReports ,
        1 AS sec_blankforms,
        Usefulinks ,
        1 AS sec_calendar,
        RCAccess ,
        1 AS sec_usefullinks,
        SUBSTRING(AccessRights, 2, 1) AS cases_general ,
        SUBSTRING(AccessRights, 3, 1) AS cases_links,
        SUBSTRING(AccessRights, 5, 1) AS case_notes,
        1 AS cases_reminders,
        SUBSTRING(AccessRights, 6, 1) AS case_documents,
        SUBSTRING(AccessRights, 8, 1) AS case_steps,
        SUBSTRING(AccessRights, 9, 1) AS cases_related,
        SUBSTRING(AccessRights, 10, 1) AS cases_main,
        SUBSTRING(AccessRights, 11, 1) AS cases_forms,
        Deletefiles ,
        0 AS activity_EB5,
        Uploadocs ,
        SecBillingedit ,
        SecBillingdelete ,
        BillingTrstaccnt 
 FROM Userlogin

Open in new window

If the above returns data, try
removed the parenthesis and the values reference.

INSERT INTO 
dbo.contact_accessrights 
		(firmid ,
        userid ,
        userloginid ,
        sec_contactmodule ,
        sec_questionnaire ,
        sec_contacts ,
        sec_cases ,
        sec_employers ,
        sec_billing ,
        sec_regionalcenters ,
        sec_agents ,
        sec_messages ,
        sec_reports ,
        sec_blankforms ,
        sec_usefullinks ,
        sec_calendar ,
        sec_resourcecenter ,
        sec_usefullinks ,
        cases_general ,
        cases_links ,
        cases_notes ,
        cases_reminders ,
        cases_documents ,
        cases_steps ,
        cases_related ,
        cases_main ,
        cases_forms,
        activity_delete ,
        activity_EB5 ,
        activity_upload ,
        billing_edit ,
        billing_delete ,
        billing_trustaccounts)

   SELECT 
  FirmId,
  UserId,
  UserLoginId,
  IndvClientModule,
        1 AS sec_questionnaire,
        CMSContacts ,
        CMSCases ,
        CMSEmployers,
        SUBSTRING(AccessRights, 7, 1) AS sec_billing ,
        0 AS sec_regionalcenters,
        0 AS sec_agents,
        1 AS sec_messages,
        CMSReports ,
        1 AS sec_blankforms,
        Usefulinks ,
        1 AS sec_calendar,
        RCAccess ,
        1 AS sec_usefullinks,
        SUBSTRING(AccessRights, 2, 1) AS cases_general ,
        SUBSTRING(AccessRights, 3, 1) AS cases_links,
        SUBSTRING(AccessRights, 5, 1) AS case_notes,
        1 AS cases_reminders,
        SUBSTRING(AccessRights, 6, 1) AS case_documents,
        SUBSTRING(AccessRights, 8, 1) AS case_steps,
        SUBSTRING(AccessRights, 9, 1) AS cases_related,
        SUBSTRING(AccessRights, 10, 1) AS cases_main,
        SUBSTRING(AccessRights, 11, 1) AS cases_forms,
        Deletefiles ,
        0 AS activity_EB5,
        Uploadocs ,
        SecBillingedit ,
        SecBillingdelete ,
        BillingTrstaccnt 
 FROM Userlogin

Open in new window

ASKER

The first one does return results. the second results in an error:

Msg 264, Level 16, State 1, Line 1
The column name 'sec_usefullinks' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'sec_usefullinks' may appear twice in the view definition.

When I remove the parenthesis from the select I have I get this error:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'SELECT'.

INSERT INTO 
dbo.contact_accessrights 
		(firmid ,
        userid ,
        userloginid ,
        sec_contactmodule ,
        sec_questionnaire ,
        sec_contacts ,
        sec_cases ,
        sec_employers ,
        sec_billing ,
        sec_regionalcenters ,
        sec_agents ,
        sec_messages ,
        sec_reports ,
        sec_blankforms ,
        sec_usefullinks ,
        sec_calendar ,
        sec_resourcecenter ,
        sec_usefullinks ,
        cases_general ,
        cases_links ,
        cases_notes ,
        cases_reminders ,
        cases_documents ,
        cases_steps ,
        cases_related ,
        cases_main ,
        cases_forms,
        activity_delete ,
        activity_EB5 ,
        activity_upload ,
        billing_edit ,
        billing_delete ,
        billing_trustaccounts)
 
 
 VALUES 
   
  SELECT 
  FirmId,
  UserId,
  UserLoginId,
  IndvClientModule,
        1 AS sec_questionnaire,
        CMSContacts ,
        CMSCases ,
        CMSEmployers,
        SUBSTRING(AccessRights, 7, 1)AS sec_billing ,
        0 AS sec_regionalcenters,
        0 AS sec_agents,
        1 AS sec_messages,
        CMSReports ,
        1 AS sec_blankforms,
        Usefulinks ,
        1 AS sec_calendar,
        RCAccess ,
        1 AS sec_usefullinks,
        SUBSTRING(AccessRights, 2, 1) AS cases_general ,
        SUBSTRING(AccessRights, 3, 1) AS cases_links,
        SUBSTRING(AccessRights, 5, 1) AS case_notes,
        1 AS cases_reminders,
        SUBSTRING(AccessRights, 6, 1) AS case_documents,
        SUBSTRING(AccessRights, 8, 1) AS case_steps,
        SUBSTRING(AccessRights, 9, 1) AS cases_related,
        SUBSTRING(AccessRights, 10, 1) AS cases_main,
        SUBSTRING(AccessRights, 11, 1) AS cases_forms,
        Deletefiles ,
        0 AS activity_EB5,
        Uploadocs ,
        SecBillingedit ,
        SecBillingdelete ,
        BillingTrstaccnt 
 FROM Userlogin

Open in new window

In this case you got a different error, there are indeed two columns using the same name in the insert side

17 and 20 this means either you will end up fewer columns on the insert side and ine too many on the select side.

Currently you had 33 columns.

ASKER

Correct, but that's the script you pasted. I am not using that one. I am using the one I copied in my last post, that doesn't have that error because the columns match. The problem is the 'select' statement, something is wrong with the syntax.

I copied and pasted from your prior post. How are the individual columns defined in the new table?

http:#a40954700
17 and 20 have the duplicate entry.

ASKER

I believe the issue is the selection of the digits.

SUBSTRING(AccessRights, 7, 1)AS sec_billing ,
0 AS sec_regionalcenters,
0 AS sec_agents,
1

SQL is taking this as ONE entry. When I select the top element it highlihgts the corresponding element below. when I get to the sec_billing it highlights all this part.

Add a space.before as and after )

is accessrights a string type or is it a numeric?

ASKER

It's a varchar, strange thing is that if I only run the select statement then it works, it shows the correct digit.
So there must be something wrong with the syntax of the select statement.

This part returns the correct values:

  SELECT
  FirmId,
  UserId,
  UserLoginId,
  IndvClientModule,
        1 AS sec_questionnaire,
        CMSContacts ,
        CMSCases ,
        CMSEmployers,
        SUBSTRING(AccessRights, 7, 1)AS sec_billing ,
        0 AS sec_regionalcenters,
        0 AS sec_agents,
        1 AS sec_messages,
        CMSReports ,
        1 AS sec_blankforms,
        1 AS sec_calendar,
        RCAccess ,
        1 AS sec_usefullinks,
        SUBSTRING(AccessRights, 2, 1) AS cases_general ,
        SUBSTRING(AccessRights, 3, 1) AS cases_links,
        SUBSTRING(AccessRights, 5, 1) AS case_notes,
        1 AS cases_reminders,
        SUBSTRING(AccessRights, 6, 1) AS case_documents,
        SUBSTRING(AccessRights, 8, 1) AS case_steps,
        SUBSTRING(AccessRights, 9, 1) AS cases_related,
        SUBSTRING(AccessRights, 10, 1) AS cases_main,
        SUBSTRING(AccessRights, 11, 1) AS cases_forms,
        Deletefiles ,
        0 AS activity_EB5,
        Uploadocs ,
        SecBillingedit ,
        SecBillingdelete ,
        BillingTrstaccnt 
 FROM Userlogin

Open in new window

-- Again, the error when running the fill script shows the word "select" in red.

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ','.

Here is another try, each and every column gas to be properly identified.

Insert into tablename select that works.
How many total columns exist in the new tablename?

ASKER

I took the name of the columns from the table and copied them here, but this query is failing before its run, its not returning an error when trying to insert, its returning an error before it actually runs.

32 columns in the table, 32 columns selected and 32 columns to insert data.

Try this,

Insert into ##temptable select columns, ...... From userlogins
Does this have the same issue?

The other option would be to export using bcp and the use bcp to load it into the new table.

ASKER

Msg 208, Level 16, State 0, Line 1
Invalid object name 'temptable'.

INSERT INTO temptable


 SELECT 
  FirmId,
  UserId,
  UserLoginId,
  IndvClientModule,
        1 AS sec_questionnaire,
        CMSContacts ,
        CMSCases ,
        CMSEmployers,
        SUBSTRING(AccessRights, 7, 1)AS sec_billing ,
        0 AS sec_regionalcenters,
        0 AS sec_agents,
        1 AS sec_messages,
        CMSReports ,
        1 AS sec_blankforms,
        1 AS sec_calendar,
        RCAccess ,
        1 AS sec_usefullinks,
        SUBSTRING(AccessRights, 2, 1) AS cases_general ,
        SUBSTRING(AccessRights, 3, 1) AS cases_links,
        SUBSTRING(AccessRights, 5, 1) AS case_notes,
        1 AS cases_reminders,
        SUBSTRING(AccessRights, 6, 1) AS case_documents,
        SUBSTRING(AccessRights, 8, 1) AS case_steps,
        SUBSTRING(AccessRights, 9, 1) AS cases_related,
        SUBSTRING(AccessRights, 10, 1) AS cases_main,
        SUBSTRING(AccessRights, 11, 1) AS cases_forms,
        Deletefiles ,
        0 AS activity_EB5,
        Uploadocs ,
        SecBillingedit ,
        SecBillingdelete ,
        BillingTrstaccnt 
 FROM Userlogin

Open in new window

You need to preppend ## to make it a tempttable that will exist until dropped.
##temptable
A single #temptable is a session temp table.
A double ##temptable can be accessed after created from any session..

ASKER

Msg 208, Level 16, State 0, Line 1
Invalid object name '##temptable'.

Try your select query that works the before from userlogin add into dbo.contact_accessrights

Select columns into dbo.contact_accessrights from userlogin

ASKER

Well .. it didn't work so not sure what you are asking. I got an error despite adding the ##

The attempt of last trying to use the select the columns with column name modifiers into contact_accessrights from userlogin a

You may need to create temp ##temptable
Then use the s.........

ASKER

So far nothing has worked. I may need to do an ASP page that will do this process. :#

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I will get back to this later.